Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
How's the usual way? –  Marian Aug 26 '11 at 14:19
1  
Your sample output is achieved by ORDER BY userID, Date. Fix your question and try again. -1 and vote to close. –  gbn Aug 26 '11 at 14:20
    
@Marian SELECT * FROM Mytable ORDER BY userID, Date –  Rajeev Vyas Aug 26 '11 at 14:23
    
@gbn i edited question.. –  Rajeev Vyas Aug 26 '11 at 14:29
    
I updated my answer –  gbn Aug 26 '11 at 14:36

3 Answers 3

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;
share|improve this answer
    
Not at all, i tried what it will do is first sort by user id and than within that it will sort by date. means users with ids smaller will always be first no matter what date is... –  Rajeev Vyas Aug 26 '11 at 14:18
    
@Rajeev Vyas: this is how it works. What aren't you telling us? –  gbn Aug 26 '11 at 14:19
    
i edited question to tell more... –  Rajeev Vyas Aug 26 '11 at 14:29
    
@gbn: works well, except for the last 2 records which are ordered by userId too, but I think it's not a problem. –  Marian Aug 26 '11 at 14:40
    
@Marian: yes, how to tie-break when first date in the same...? –  gbn Aug 26 '11 at 14:42

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

share|improve this answer
    
Wonder what the performance impact of using that IFNULL() in the order by clause is, on a table with millions of rows. Of course the entire thing looks complex, but the IFNULL is just what jumps out at me! –  Derek Downey Aug 26 '11 at 15:32
1  
@DTest - IMHO This query fits this data set to a tee. I have no clue what performance impact could possibly be. I am sure it has O(n^2) written all over it. However, there was a great performance impact just thinking about this solution. –  RolandoMySQLDBA Aug 26 '11 at 16:03

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
share|improve this answer
    
Not at all, i tried what it will do is first sort by user id and than within that it will sort by date. means users with ids smaller will always be first no matter what date is... –  Rajeev Vyas Aug 26 '11 at 14:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.