Clever Geek Handbook

SQL: How to get the ordering of records by date and id with date precedence?

I have a table with the following content:

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 get records from a table ordering by date and event_id, with date order prevailing, eg. today he writes down the first, yesterday he writes down the second, and so on.

When I run the query below, the records are displayed in the wrong order as per my requirement. I need to display the record first Drama Club

, because I created the last record in Drama Club

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

(misordered) 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 result.

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
+3


source to share


1 answer


UPDATED # 2015-01-05 / 1

I think I got the concept now - try this:

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
+1


source






More articles:


All Articles