开发者

MySQL - Is it possible to achieve this using SQL?

Imagine you've got a table with 2 columns: ID and NAME. ID is simply a number, incrementing for each row (as you'd expect). NAME is some random varchar string. NAME can 开发者_如何学Gobe same for different rows. Now, imagine you want to get the 3 latest occurences in this table, where NAME only may occur once.

For example, if you've got this data:

ID  NAME
1   HELLO
2   TEST
3   HELLO
4   HELLO
5   QWERTY
6   HELLO

Then the result of the question should be:

6   HELLO
5   QWERTY
2   TEST

Is it possible achieve this on SQL level?


SELECT 
   MAX(ID), 
   Name
FROM 
   table
GROUP BY 
   Name
ORDER BY
   MAX(ID) desc
LIMIT 3


SELECT MAX(ID), NAME
FROM THAT_TABLE
GROUP BY NAME

See: GROUP BY (Aggregate) Functions


I suppose, you need to use "DISTINCT" for the "name" column:

SELECT DISTINCT name, id FROM table_name ORDER BY id DESC LIMIT 3;

Another way to achieve this is to use "GROUP BY" for "name" (see another answer)

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜