HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Want to order on two columns but not the usual way

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
wantusualorderthecolumnsbutwaytwonot

Problem

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...

Solution

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;

Code Snippets

SELECT *
FROM Mytable
ORDER BY
   userID, Date
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;

Context

StackExchange Database Administrators Q#5081, answer score: 5

Revisions (0)

No revisions yet.