Want to order on two columns but not the usual way

Question

I have a table as below

ID  userID  Date
1   2273    22/08/2011
2   2274    24/08/2011
3   2275    26/08/2011
4   2273    26/08/2011
5   2273    26/08/2011
6   2271    26/08/2011

And want result as below. Need mysql query help to acheive this. Basically i want to order by date but same user must all be together. And i am trying to achieve this in single query.

ID  userID  Date
1   2273    22/08/2011
4   2273    26/08/2011
5   2273    26/08/2011
2   2274    24/08/2011
3   2275    26/08/2011
6   2271    26/08/2011

I tries select * from mytable order by userId,Date and will result in following

ID  userID  Date
6   2271    26/08/2011
1   2273    22/08/2011
4   2273    26/08/2011
5   2273    26/08/2011
2   2274    24/08/2011
3   2275    26/08/2011

Which is not i want i want user with date ascending should come first and same user records in table should follow his first record...

Answer 1

SELECT *
FROM Mytable
ORDER BY
   userID, Date

I assume Date is really a date/time type and not varchar...

Edit, after clarification:

Untested

SELECT
    M.*
FROM
    ( --one row for each user
    SELECT MIN(Date) AS FirstUserDate, userID
    FROM MyTable
    GROUP BY userID
    ) foo
    JOIN
    MyTable M ON foo.userID = M.userID
ORDER BY
    foo.FirstUserDate, M.userID, M.Date;

Answer 2

I think this query will do it for you

SELECT A.*
FROM userorder A LEFT JOIN 
(SELECT * FROM userorder
WHERE userID=
(SELECT MIN(userID) FROM userorder
WHERE Date=(SELECT MAX(Date)
FROM userorder ORDER BY userID))) B
USING (ID)
ORDER BY IFNULL(B.ID,-1),userId,Date;

I actually used your sample data and tried it in MySQL 5.5.12 on my PC

mysql> use test
Database changed
mysql> DROP TABLE IF EXISTS userorder;
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE userorder (ID int,userID int,Date date);
Query OK, 0 rows affected (0.16 sec)

mysql> INSERT INTO userorder VALUES
    -> (1,2273,'2011-08-22'),
    -> (2,2274,'2011-08-24'),
    -> (3,2275,'2011-08-26'),
    -> (4,2273,'2011-08-26'),
    -> (5,2273,'2011-08-26'),
    -> (6,2271,'2011-08-26');
Query OK, 6 rows affected (0.05 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM userorder;
+------+--------+------------+
| ID   | userID | Date       |
+------+--------+------------+
|    1 |   2273 | 2011-08-22 |
|    2 |   2274 | 2011-08-24 |
|    3 |   2275 | 2011-08-26 |
|    4 |   2273 | 2011-08-26 |
|    5 |   2273 | 2011-08-26 |
|    6 |   2271 | 2011-08-26 |
+------+--------+------------+
6 rows in set (0.00 sec)

mysql> SELECT A.*
    -> FROM userorder A LEFT JOIN
    -> (SELECT * FROM userorder
    -> WHERE userID=
    -> (SELECT MIN(userID) FROM userorder
    -> WHERE Date=(SELECT MAX(Date)
    -> FROM userorder ORDER BY userID))) B
    -> USING (ID)
    -> ORDER BY IFNULL(B.ID,-1),userId,Date;
+------+--------+------------+
| ID   | userID | Date       |
+------+--------+------------+
|    1 |   2273 | 2011-08-22 |
|    4 |   2273 | 2011-08-26 |
|    5 |   2273 | 2011-08-26 |
|    2 |   2274 | 2011-08-24 |
|    3 |   2275 | 2011-08-26 |
|    6 |   2271 | 2011-08-26 |
+------+--------+------------+
6 rows in set (0.00 sec)

Give it a Try !!!

Answer 3

Does this do what you're looking for? It's just a sort by userID and Date.

select ID, userID, Date
from Table
order by userID, Date