patternsqlMinor
Want to order on two columns but not the usual way
Viewed 0 times
wantusualorderthecolumnsbutwaytwonot
Problem
I have a table as below
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.
I tries select * from mytable order by userId,Date and will result in following
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...
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/2011And 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/2011I 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/2011Which 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, DateI 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, DateSELECT
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.