Get ONLY latest value for each unique value on 1 column

Question

I have the following demo table

cid  uid  unixdate
110   90  129
109   85  128
108   81  127
107   90  126
106   85  125
105   70  124

I want to create an sql select to get only 1 row for each uid which would look like this

cid  uid  unixdate
110   90  129
109   85  128
108   81  127
105   70  124

But the result should be the LATEST entries (sort with unixdate DESC)

Have tried all from this example which looks very similar like for example.these queries

SELECT * FROM demo GROUP BY uid ORDER BY unixdate DESC

SELECT * From demo GROUP BY uid HAVING COUNT(*) >=1 order by unixdate DESC

but they don't get the latest but the first entry for each UID (instead of the latest)

NOTE running mysql Server version: 5.7.21-0ubuntu0.16.04.1 - (Ubuntu) and doing the sql queries using phpmyadmin interface

What happened when you tried that example? What couldn't you get working? — Alfabravo, Apr 18 '18 at 20:17
Possible duplicate of [Is there any difference between GROUP BY and DISTINCT](https://stackoverflow.com/questions/164319/is-there-any-difference-between-group-by-and-distinct) — Martin, Apr 18 '18 at 20:17
You want to look up [MySQL `GROUP BY`](https://www.guru99.com/group-by.html) . — Martin, Apr 18 '18 at 20:18

score 0 · Answer 1 · answered Apr 18 '18 at 20:31

0

Try like this:

SELECT * FROM demo GROUP BY uid ORDER BY unixdate DESC;

where demo is the name of the table.

answered Apr 18 '18 at 20:31

The query is all OK, check the SQL Fiddle link posted in the answer. This is working fine as you expected. May be you should check the building schema carefully whether you have created it exactly like my example in SQL Fiddle in your system. – Apr 19 '18 at 04:32
Optionally, there might be some problems in your mysql installation. Please follow this link: https://stackoverflow.com/questions/37951742/1055-expression-of-select-list-is-not-in-group-by-clause-and-contains-nonaggr to fix it. – Apr 19 '18 at 04:33

score 0 · Accepted Answer · answered Apr 19 '18 at 09:52

0

The problem is the group by will always return the first record in the group on the result set. This is the solution that worked.

SELECT * FROM demo WHERE unixdate IN 
( SELECT MAX(unixdate) FROM demo GROUP BY uid ) 
ORDER BY unixdate

answered Apr 19 '18 at 09:52

GiorgosK

2 Answers2