Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

ORDER BY first mention, then by another column

Tags:

mysql

Let's say I have a table called 'messages':

+-----------+--------+
| timestamp | poster |
+-----------+--------+
|         1 | John   |
|         2 | Peter  |
|         3 | Peter  |
|         4 | Chris  |
|         5 | John   |
+-----------+--------+

I want a query to first list all posts by the first poster, then all by the second poster, etc. without using a subquery. The first poster is determined by the timestamp. Find out who is first and return all their posts, then who is second:

+-----------+--------+
| timestamp | poster |
+-----------+--------+
|         1 | John   |
|         5 | John   |
|         2 | Peter  |
|         3 | Peter  |
|         4 | Chris  |
+-----------+--------+

EDIT
Ok guys, I made the question way too simple. The very basic examples you are giving work because (J)ohn comes before (P)eter. What if Peter was first? You would need to add DESC to the poster clause. Your SQL statements using the simple ORDER BY do not address the issue of ordering by who is first, second, third, etc.

EDIT AGAIN
I added "Chris" to the mix to make it more plain as to what I want. John was the first poster; get all his posts. Then Peter because he is second. Then Chris.

like image 535
JFriend Avatar asked Jan 22 '26 01:01

JFriend


2 Answers

I think there's no other way than to create another result set and join with that:

SELECT poster, MIN(timestamp) AS first_ts
FROM messages
GROUP BY poster
ORDER BY first_ts;

Result:

+--------+-----------+
| poster | first_ts  |
+--------+-----------+
| John   | 1         |
| Peter  | 2         |
| Chris  | 4         |
+--------+-----------+
like image 153
Ja͢ck Avatar answered Jan 24 '26 20:01

Ja͢ck


can't think of a way to do without a subquery,

but if you can accept your answer as timestamps of csv list. then here is one way.

SELECT poster,
       GROUP_CONCAT(timestamp ORDER BY timestamp) as timestamps       
FROM messages
GROUP BY poster
ORDER BY MIN(timestamp)

sqlFiddle

like image 22
Tin Tran Avatar answered Jan 24 '26 21:01

Tin Tran