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.
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 |
+--------+-----------+
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With