How can remove duplicate rows after execute a SQL Query

Question

I create this SQL query:

SELECT * FROM favorite_user_cellphone fc
WHERE user_id = 123 AND unfavorited_at is null
ORDER BY favorited_at DESC;

the result of this query is below:

favorite_id  | user_id |  cellphone_id |  favorited_at        |   unfavorited_at
     7           123         1225       2019-04-13 12:35:02     null
     5           123         1225       2019-04-11 12:35:02     null
     2           123         1275       2019-04-09 13:00:02     null
     1           123         1225       2019-04-09 12:35:02     null

But now I would like to remove the duplicate rows (column cellphone_id) and keep the row with the most current date for each cellphone_id

So, the final result should be like this:

favorite_id  | user_id |  cellphone_id |  favorited_at        |   unfavorited_at
     7           123         1225       2019-04-13 12:35:02     null
     2           123         1275       2019-04-09 13:00:02     null

I do it:

SELECT * FROM favorite_user_cellphone fc
WHERE user_id = 123 AND unfavorited_at is null
ORDER BY favorited_at DESC;

I would like know how to create the query my friend to get this information. — André Ribeiro, Apr 12 '19 at 15:33

score 1 · Answer 1 · answered Apr 12 '19 at 15:22

You could use aggreagtion function max() and group by

SELECT max(favorite_id), user_id cellphone_id, max(favorited_at)  
FROM favorite_user_cellphone fc
WHERE user_id = 123 AND unfavorited_at is null
GROUP BY cellphone_id, user_id 
ORDER BY favorited_at DESC;

if you use select * all the records are returned also for info that can produce undesidered result .. so you should select only the columns you need applying aggregation functio for reduce result

Eray Balkanli · Accepted Answer · 2019-04-12T15:53:42.987

1

Another solution, that you can run on your existing result table, might be:

select fuc.*
from favorite_user_cellphone fuc
inner join
(
   select cellphone_id, 
          max(favorite_id) as favorite_id
   from favorite_user_cellphone
   where user_id = 123
   group by cellphone_id
) t on fuc.favorite_id = t.favorite_id

edited Apr 12 '19 at 15:53

answered Apr 12 '19 at 15:32

Eray Balkanli

7,752
11
48
82

It's working, but suppose that I need get the info from specifically user_id. this query get the rows from any user_id – André Ribeiro Apr 12 '19 at 15:46
@AndréRibeiro then you need a where clause, user_id = 123. Updated my answer – Eray Balkanli Apr 12 '19 at 15:53
1

thank so much it's working. – André Ribeiro Apr 12 '19 at 18:43
I'm glad to help. – Eray Balkanli Apr 12 '19 at 19:19

How can remove duplicate rows after execute a SQL Query

2 Answers2