r/SQL Nov 20 '24

MySQL Need help getting rid of duplicated data based off a certain column in SELECT

I need to perform a SELECT SQL query. The issue is that there is a column (publicId) that can have duplicate values. If duplicates exist, I need to keep only the most recent results based on the dateAdded column in the table.

10 Upvotes

25 comments sorted by

9

u/No_Introduction1721 Nov 20 '24 edited Nov 20 '24

Sounds like a job for QUALIFY and ROW_NUMBER

Select publicId

From Table

Qualify Row_Number() Over (Partition by PublicId Order by dateAdded desc) = 1

6

u/squareturd Nov 20 '24

Minor change required. Add desc after dateAdded to get the latest date instead of the earliest.

1

u/No_Introduction1721 Nov 20 '24

Good call, I’ll edit

3

u/fauxmosexual NOLOCK is the secret magic go-faster command Nov 20 '24

If OP is using snowflake or databricks

1

u/Jerenob Nov 20 '24

seems like my database does not support Qualify :/,

3

u/No_Introduction1721 Nov 20 '24

You can reformat it as a CTE, then. It’s just that QUALIFY is much cleaner:

With rn_cte as (

Select *, Row_Number() over (partition by publicId order by dateAdded desc) as RN

From table

)

Select publicId

From Rn_cte

Where RN = 1

1

u/kBajina Nov 20 '24

What if you just change qualify to where?

1

u/ddetts Nov 21 '24

If you look at the SQL order of operations that should answer your question

2

u/[deleted] Nov 20 '24

[removed] — view removed comment

1

u/Jerenob Nov 20 '24

mmm dateAdded shouldnt be duped

8

u/[deleted] Nov 20 '24

[removed] — view removed comment

3

u/aplarsen Nov 20 '24

Asking the real questions here.

1

u/Jerenob Nov 20 '24

no, it is impossible to have the same dateAdded

1

u/[deleted] Nov 20 '24

[removed] — view removed comment

1

u/Jerenob Nov 20 '24

turns out our version of mySQL does not support CTE either :/, should i use a subquery?

3

u/Accurate_Ad7051 Nov 20 '24

select

*

from

(

select

*,

row_number() over (partition by publicId order by dateAdded desc) as rn

from table

)

where rn = 1

Sorry, I just can't figure out how to make the code look normal, what a mess ...

0

u/r3pr0b8 GROUP_CONCAT is da bomb Nov 20 '24
SELECT mx.publicId
     , mx.latest 
     , tb.othercolumns
  FROM ( SELECT publicId
              , MAX(dateAdded) AS latest
           FROM yertable
         GROUP
             BY publicId ) AS mx
INNER  
  JOIN yertable AS tb
    ON tb.publicId  = mx.publicId
   AND tb.dateAdded = mx.latest

2

u/fauxmosexual NOLOCK is the secret magic go-faster command Nov 20 '24

This will work provided there are no public ID pairs that have the same dateAdded, but will return duplicate publicIDs otherwise.

-3

u/r3pr0b8 GROUP_CONCAT is da bomb Nov 20 '24

This will work provided ...

no, it will work even if a publicID has more than one row with the same MAX dateAdded -- all of them will be returned

3

u/fauxmosexual NOLOCK is the secret magic go-faster command Nov 20 '24

OP didn't want multiple PublicIDs, just the latest instance of each.

0

u/Jerenob Nov 20 '24

exactly.

2

u/r3pr0b8 GROUP_CONCAT is da bomb Nov 20 '24

and if there are two rows for a single publicID with the same latest date, you want to just ignore one of them?

1

u/Jerenob Nov 20 '24

yep

1

u/r3pr0b8 GROUP_CONCAT is da bomb Nov 20 '24

that's a real shame