CODEWITHSUNDEEP
mysqlsqlsql-order-by
Ray
Ray

Reputation: 487

Ordering of rows chronological by one field, then chronological by another field

If we have a table that logs when users sign in,

---------------------------------
| user    |  time               |
---------------------------------
| Jill    | 2014-09-13 21:04:36 |
| Jack    | 2014-09-13 21:06:43 |
| Jack    | 2014-09-13 21:09:13 |
| Jack    | 2014-09-13 21:12:23 |
| Jill    | 2014-09-13 21:13:41 |
---------------------------------

and we want to order the rows by which user logged in first, and then every subsequent login by that user ordered by time,

---------------------------------
| user    |  time               |
---------------------------------
| Jill    | 2014-09-13 21:04:36 |
| Jill    | 2014-09-13 21:13:41 |
| Jack    | 2014-09-13 21:06:43 |
| Jack    | 2014-09-13 21:09:13 |
| Jack    | 2014-09-13 21:12:23 |
---------------------------------

how could we write the ORDER BY clause to do that?

If we use ORDER BY name, time then we get Jack before Jill based on alphabetic sort of name.

---------------------------------
| user    |  time               |
---------------------------------
| Jack    | 2014-09-13 21:06:43 |
| Jack    | 2014-09-13 21:09:13 |
| Jack    | 2014-09-13 21:12:23 |
| Jill    | 2014-09-13 21:04:36 |
| Jill    | 2014-09-13 21:13:41 |
---------------------------------

If we use ORDER BY time, name then we just get the names mixed in whatever order they signed in.

---------------------------------
| user    |  time               |
---------------------------------
| Jill    | 2014-09-13 21:04:36 |
| Jack    | 2014-09-13 21:06:43 |
| Jack    | 2014-09-13 21:09:13 |
| Jack    | 2014-09-13 21:12:23 |
| Jill    | 2014-09-13 21:13:41 |
---------------------------------

We could use SELECT DISTINCT name FROM table to get only the names in chronological order, and then somehow get the rest of the rows... but how to do it in one query? Is there a way to do a subquery in an ORDER BY clause? ORDER BY (SELECT DISTINCT name FROM table) doesn't work.

I feel like this should be simple, but I can't figure it out. Any suggestions?

Upvotes: 0

Views: 35

Answers (1)

Joachim Isaksson
Joachim Isaksson

Reputation: 180987

You could join with a subquery which gets the minimum log in time for a user, and just order by that time. The ordering by a.user is required in case two users have the same first login time so that the results won't be intermixed.

SELECT a.* 
FROM table1 a
JOIN ( SELECT user, MIN(time) min_time FROM table1 GROUP BY user) b
  ON a.user=b.user
ORDER BY b.min_time, a.user, a.time

An SQLfiddle to test with.

Upvotes: 1

Related Questions