每个唯一列值获取一条记录 [英] 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 |

我需要获取ID为2、4和6的行.

我需要为每个systemid收集1条记录，并且它必须是最早(最年轻)的记录，因此在这种情况下，它是ID最高的记录.我一直在探索GROUP BY，ORDER BY和LIMIT，但没有得到想要的结果.您如何在一个列中为每个单独的值收集一条记录，并确保它是具有最高ID的记录?

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

推荐答案

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

请注意，使用GROUP BY时，您选择的所有列都必须位于GROUP BY子句中或包装在汇总函数中，例如最大值，最小值，总和或平均值.

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.

这篇关于每个唯一列值获取一条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！