CODEWITHSUNDEEP
mysqldatetime
Sam
Sam

Reputation: 23

MySQL Custom Ordering (datetime and join table)

Let's say I have a list of presentations meant to be played on a given day, following this data structure:

ID      DateTime             Title          StreamID
--------------------------------------------------------
2268    2015-07-21 07:45:00  Introduction   NULL
2229    2015-07-21 09:00:00  A              1   
2259    2015-07-21 09:00:00  D              2
2262    2015-07-21 09:00:00  G              3   
2230    2015-07-21 10:00:00  B              1   
2260    2015-07-21 10:00:00  E              2   
2263    2015-07-21 10:00:00  H              3   
2261    2015-07-21 11:00:00  F              2   
2264    2015-07-21 11:00:00  I              3   
2250    2015-07-21 11:00:00  C              1   
2267    2015-07-21 18:00:00  Outro          NULL

Some presentations are part of streams, some aren't. Streams are a way of grouping presentations in a parallel manner (the StreamID is joined as part of my query)

I would like to be able to order them as following: By datetime, but if they are part of a stream, then order presentations by datetime BUT ALSO per stream id FIRST So like as followed:

ID      DateTime             Title          StreamID
--------------------------------------------------------
2268    2015-07-21 07:45:00  Introduction   NULL
2229    2015-07-21 09:00:00  A              1   
2230    2015-07-21 10:00:00  B              1
2250    2015-07-21 11:00:00  C              1   
2259    2015-07-21 09:00:00  D              2
2260    2015-07-21 10:00:00  E              2   
2261    2015-07-21 11:00:00  F              2   
2262    2015-07-21 09:00:00  G              3   
2263    2015-07-21 10:00:00  H              3   
2264    2015-07-21 11:00:00  I              3   
2267    2015-07-21 18:00:00  Outro          NULL

Hope that makes sense.

I have tried the following queries but without success: 

SELECT * FROM presentation
LEFT JOIN stream ON stream.ID = presentation.StreamID
ORDER BY DateTime, StreamID

AND 

SELECT * FROM presentation
LEFT JOIN stream ON stream.ID = presentation.StreamID
ORDER BY StreamID, DateTime

Thanks for your responses

Upvotes: 2

Views: 60

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72195

You can use the following query:

SELECT p.*
FROM presentation AS p 
INNER JOIN (SELECT COALESCE(StreamID, ID) AS ID, MIN(`DateTime`) AS minDate
            FROM presentation
            GROUP BY COALESCE(StreamID, ID))
AS t ON COALESCE(p.StreamID, p.ID) = t.ID
ORDER BY minDate, p.StreamID, p.DateTime

The above query employs a derived table that groups records per StreamID, or, in case of NULL, per ID. The minimum DateTime is selected for each of these groups. Joining this derived table back to the original table, we can achieve group sorting, i.e. sort every group by date, wherein a row with a NULL value of StreamID is considered as a separate group.

Note: For the sake of simplicity Stream table has been left out of the query.

Demo here

Upvotes: 1

Related Questions