CODEWITHSUNDEEP
mysqlsqlselect
faressoft
faressoft

Reputation: 19651

How to combine select distinct and order by a non-selected attribute or any alternative way?

How to combine select distinct and order by a non-selected attribute or any alternative way?

I have a table called message

+----+------+-----------+-------------+
| id | body | sender_id | receiver_id |
+----+------+-----------+-------------+
| 10 | ...  |         1 |           2 |
| 28 | ...  |         1 |           3 |
| 29 | ...  |         2 |           1 |
| 30 | ...  |         2 |           1 |
| 31 | ...  |         1 |           2 |
| 32 | ...  |         3 |           1 |
| 37 | ...  |         1 |          47 |
+----+------+-----------+-------------+

Is there a way to select a unique list of the latest receiver_ids (ORDER BY id DESC) and limiting the list by 10 items ?

The result should be

+-------------+
| receiver_id |
+-------------+
|          47 |
|           1 |
|           2 |
|           3 |
+-------------+

As we know:

SELECT DISTINCT receiver_id FROM message ORDER BY id

Is not a valid sql because the id attribute must be in the select list

Upvotes: 2

Views: 111

Answers (4)

ernest_k
ernest_k

Reputation: 45309

SELECT receiver_id
FROM message
group by receiver_id
ORDER BY id DESC
limit 10

This will give you distinct receiver_id values, ordered by receiver ID. MySql seems to be consistent in ordering, regardless of the row limit.

You can play with a similar example here: http://sqlfiddle.com/#!9/dcb16/35918

Upvotes: -1

Ramya
Ramya

Reputation: 21

SELECT TOP 10 a.receiver_id FROM ( SELECT id , receiver_id , ROW_NUMBER() OVER ( PARTITION BY receiver_id ORDER BY id DESC ) [rank] FROM message ) a WHERE a.rank = 1 ORDER BY a.id DESC

This is t-sql query

Upvotes: 0

user359040
user359040

Reputation:

Here's one way to do it:

select receiver_id from 
(select receiver_id, max(id) max_id from message group by receiver_id) ilv 
order by max_id desc limit 10;

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269623

I'm not sure what "order by not-selected attribute" means. But the basic query can be handled using where and a subquery (or a join and explicit aggregation):

select m.receiver_id
from message m
where m.id = (select max(m2.id) from message m2 where m2.sender = m.sender)
order by id desc;

I am guessing that the ordering criterion is id desc.

Upvotes: 0

Related Questions