Get last 3 rows from SQL table without duplicates of a row

user_19185016 0
Viewed 31

Lets say we have a table that looks like this:

+---------------+----------------+-------------------+
| ID            | random_string  | time              |
+---------------+----------------+-------------------+
| 2             | K2K3KD9AJ      |2022-07-21 20:41:15|
| 1             | SJQJ8JD0W      |2022-07-17 23:46:13|
| 1             | JSDOAJD8       |2022-07-11 02:52:21|
| 3             | KPWJOFPSS      |2022-07-11 02:51:57|
| 1             | DA8HWD8HHD     |2022-07-11 02:51:49|
------------------------------------------------------

I want to select the last 3 entries into the table, however they must all have separate ID's.

Expected Result:

+---------------+----------------+-------------------+
| ID            | random_string  | time              |
+---------------+----------------+-------------------+
| 2             | K2K3KD9AJ      |2022-07-21 20:41:15|
| 1             | SJQJ8JD0W      |2022-07-17 23:46:13|
| 3             | KPWJOFPSS      |2022-07-11 02:51:57|
------------------------------------------------------

I have already tried: SELECT DISTINCT id FROM table ORDER BY time DESC LIMIT 3;

And: SELECT MIN(id) as id FROM table GROUP BY time DESC LIMIT 3;

mysql sql
2 Answers

Using MySql 8 an easy solution is to assign a row number using a window:

select Id, random_string, time
from (
  select *, Row_Number() over(partition by id order by time desc) rn
  from t
)t
where rn = 1
order by time desc
limit 3;

See Demo

user_153326500

If you're not on MySQL 8, then I have two suggestions.

  1. Using EXISTS:
SELECT m1.ID,
       m1.random_string,
       m1.time
FROM mytable m1
WHERE EXISTS
  (SELECT ID
    FROM mytable AS m2
    GROUP BY ID
    HAVING m1.ID=m2.ID 
      AND m1.time= MAX(time)
          )
  1. Using JOIN:
SELECT m1.ID,
       m1.random_string,
       m1.time
FROM mytable m1
JOIN
  (SELECT ID, MAX(time) AS mxtime
    FROM mytable 
    GROUP BY ID) AS m2
 ON m1.ID=m2.ID
   AND m1.time=m2.mxtime

I've not test in large data so don't know which will perform better (speed) however this should return the same result:

Here's a fiddle

Of course, this is considering that there will be no duplicate of exact same ID and time value; which seems to be very unlikely but still it's possible.

user_109106920
Related