CODEWITHSUNDEEP
mysqlsqlgroup-bysql-order-bylimit
Arbiter
Arbiter

Reputation: 504

Get one record per unique column value

I have a table that list system licences, multiple licences for each system (the expired ones and existing ones). I've only posted two columns in this question as they're the only important ones. 

| id | systemid | 
|  1 |        1 |
|  2 |        1 |
|  3 |        2 |
|  4 |        2 |
|  5 |        3 |
|  6 |        3 |

I need to get the rows with the id of 2, 4 and 6.

I need to collect 1 record for each systemid and it has to be the earliest (youngest) record, so in this case, the record with the highest id. I've been exploring GROUP BY, ORDER BY and LIMIT but I'm not producing the result I'm after. How do you collect one record for each individual value in one column and make sure it's the record with the highest id?

I KNOW this is wrong, but it's what I'm currently starring at:

SELECT * FROM licences GROUP BY systemid ORDER BY id DESC LIMIT 1

Upvotes: 1

Views: 105

Answers (2)

netopiax
netopiax

Reputation: 154

SELECT max(id), systemid FROM table GROUP BY systemid

Note that with a GROUP BY, all columns you select must either be in the GROUP BY clause or wrapped in an aggregating function, like max, min, sum, or average.

Upvotes: 1

Tim Peters
Tim Peters

Reputation: 255

This will grab the highest id per systemid.

SELECT MAX(id), systemid
FROM ...
GROUP BY systemid

Upvotes: 1

Related Questions