user3310782 user3310782 - 3 years ago 76
MySQL Question

Display only one row for identical entries in mysql

Let's say you have a table with multiple identical entries and you want to show only the first one of each.

userid | event
1 01-01-2017
2 09-12-2016
2 05-06-2017
2 09-12-2016
3 10-03-2017
...


and you want to display only one date (e.g. the minimal one per user):

userid | event
1 01-01-2017
2 09-12-2016
3 10-03-2017
....


Normally I would try a subquery:

SELECT userid, event FROM table as A
WHERE event=(SELECT min(event) FROM table as B WHERE A.userid = B.userid )


but in mySQL this displays 2 rows for userid=2, since that user has 2 identical minimal dates.

How can you limit the no. of rows to just one?

Answer Source
SELECT userid, event
FROM YourTable t
WHERE NOT EXISTS (
   SELECT * FROM YourTable t WHERE t.userid = userid AND event > t.event
)

OR

SELECT a.id, a.rev, a.contents
  FROM (SELECT userid, Event,
               ROW_NUMBER() OVER (PARTITION BY Event ORDER BY userid DESC) rank
          FROM YourTable) a
 WHERE a.rank = 1
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download