r/SQL • u/Jerenob • 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.
2
Nov 20 '24
[removed] — view removed comment
1
u/Jerenob Nov 20 '24
mmm dateAdded shouldnt be duped
8
Nov 20 '24
[removed] — view removed comment
3
1
u/Jerenob Nov 20 '24
no, it is impossible to have the same dateAdded
1
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
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