CODEWITHSUNDEEP
sqlmysqlgroup-byaggregate-functions
quano
quano

Reputation: 19152

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 be 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?

Upvotes: 1

Views: 82

Answers (3)

Salman Arshad
Salman Arshad

Reputation: 272386

SELECT MAX(ID), NAME
FROM THAT_TABLE
GROUP BY NAME
ORDER BY 1 DESC

See: GROUP BY (Aggregate) Functions

Upvotes: 3

Macy Abbey
Macy Abbey

Reputation: 3887

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

Upvotes: 5

Kel
Kel

Reputation: 7790

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)

Upvotes: 0

Related Questions