CODEWITHSUNDEEP
mysqlsql
Mehul Dudhat
Mehul Dudhat

Reputation: 431

SQL: How to retrieve records ordering by date and ID for with date order predominating?

I have a table with the following contents:

id  Event_id    Event_name  Entry_time

1   2           Cricket     2015-01-03 09:58:08
2   2           Cricket     2015-01-03 09:58:08
3   1           Drama Club  2015-01-03 09:58:37
4   1           Drama Club  2015-01-03 09:58:37
5   NULL        NULL        2015-01-03 09:58:58
6   2           Cricket     2015-01-03 10:00:32
7   2           Cricket     2015-01-03 10:00:32
8   1           Drama Club  2015-01-03 10:01:40
9   1           Drama Club  2015-01-03 10:01:40
10  2           Cricket     2015-01-04 10:03:40
11  1           Drama Club  2015-01-04 10:01:40

I need to retrieve records from the table ordering by date and event_id, with the date order predominating, eg. today's records first, yesterday's records second and so on.

When I execute the query below, the records appear in wrong order as per my requirement. I need to display Drama Club record first because I created last entry in Drama club. If any one has an idea where I am going wrong, please help me.

SELECT * 
FROM  `event_info` 
ORDER BY DATE( Entry_time ) DESC , Event_id DESC , Event_id DESC

(wrong-ordered) result:

id  Event_id    Event_name  Entry_time

1   2           Cricket     2015-01-03 09:58:08
2   2           Cricket     2015-01-03 09:58:08
6   2           Cricket     2015-01-03 10:00:32
7   2           Cricket     2015-01-03 10:00:32
3   1           Drama Club  2015-01-03 09:58:37
4   1           Drama Club  2015-01-03 09:58:37
8   1           Drama Club  2015-01-03 10:01:40
9   1           Drama Club  2015-01-03 10:01:40
5   NULL        NULL    2015-01-03 09:58:58

I need below output.

id  Event_id    Event_name  Entry_time   
10  2           Cricket     2015-01-04 10:03:40
11  1           Drama Club  2015-01-04 10:01:40 //4th  jan section

3   1           Drama Club  2015-01-03 09:58:37    
4   1           Drama Club  2015-01-03 09:58:37    
8   1           Drama Club  2015-01-03 10:01:40    
9   1           Drama Club  2015-01-03 10:01:40    
1   2           Cricket     2015-01-03 09:58:08    
2   2           Cricket     2015-01-03 09:58:08
6   2           Cricket     2015-01-03 10:00:32    
7   2           Cricket     2015-01-03 10:00:32    
5   NULL        NULL        2015-01-03 09:58:58 //3rd jan section

Upvotes: 1

Views: 822

Answers (1)

user2941651
user2941651

Reputation:

UPDATED #2015-01-05 / 1

I think that now I understood the concept - please give the following a try:

SELECT 
    A.* 
FROM  
    `event_info` AS A 
    LEFT OUTER JOIN
    (
        SELECT 
            Event_id,
            DATE(Entry_time) AS _Event_Date,
            MAX(TIME(Entry_time)) AS Event_Max_Entry_Time
        FROM  
            `event_info`
        GROUP BY 
            Event_id, DATE(Entry_time)
    ) AS B
      ON A.Event_id = B.Event_id
          AND DATE(A.Entry_time) = B._Event_Date  
ORDER BY 
    DATE(A.Entry_time) DESC, 
    B.Event_Max_Entry_Time DESC, 
    A.Event_id DESC, 
    A.Entry_time ASC, 
    A.Event_id DESC

The above query gave me the exact new expected set:

ID  EVENT_ID    EVENT_NAME  ENTRY_TIME

10  2           Cricket     2015-01-04 10:03:40
11  1           Drama Club  2015-01-04 10:01:40 
3   1           Drama Club  2015-01-03 09:58:37
4   1           Drama Club  2015-01-03 09:58:37
8   1           Drama Club  2015-01-03 10:01:40
9   1           Drama Club  2015-01-03 10:01:40
2   2           Cricket     2015-01-03 09:58:08
1   2           Cricket     2015-01-03 09:58:08
7   2           Cricket     2015-01-03 10:00:32
6   2           Cricket     2015-01-03 10:00:32
5   NULL        NULL        2015-01-03 09:58:58

Upvotes: 1

Related Questions