sort records by status and datetime asc or desc-CodePudding

Guide me here, if you can: I'm using mysql.

I need to order Events according to the sequence:

the ones that are happening: status = started (and datetime ASC)

2021-12-07 07:00:00 | started
2021-12-07 08:00:00 | started
2021-12-07 09:00:00 | started

the ones that will happen: status = created (and datetime asc)

2021-12-07 13:00:00 | created
2021-12-07 14:00:00 | created
2021-12-07 15:00:00 | created

those that have passed the date but have not started (and datetime desc)

2021-12-07 06:00:00 | created
2021-12-07 05:00:00 | created
2021-12-07 04:00:00 | created

the finished ones: status = finished (and datetime desc)

2021-12-06 12:00:00 | finished
2021-12-05 17:00:00 | finished
2021-12-04 19:00:00 | finished

I'm trying with some CASE WHEN...

order by
  CASE
    WHEN E.status = 'started' THEN 1
    WHEN E.status = 'created' && eventDateTime >= NOW() THEN 2
    WHEN E.status = 'created' && eventDateTime < NOW() THEN 3
    WHEN E.status = 'finished' THEN 4
    ELSE 5
  END

But I didn't know how to match THEN 1 && eventDateTime ASC or THEN 4 && eventDateTime DESC

CodePudding user response：

Assume that you have three virtual columns:

Column 1 contains the value 1, 2, 3 or 4 depending on the four conditions
Column 2 contains eventDateTime when condition 1 or 2 is true
Column 3 contains eventDateTime when condition 3 or 4 is true

Your order by clause needs to be written as:

ORDER BY
    CASE
        WHEN E.status = 'started' THEN 1
        WHEN E.status = 'created' AND eventdatetime >= current_timestamp THEN 2
        WHEN E.status = 'created' AND eventdatetime < current_timestamp THEN 3
        WHEN E.status = 'finished' THEN 4
        ELSE 5
    END,
    CASE
        WHEN E.status = 'started' THEN eventdatetime
        WHEN E.status = 'created' AND eventdatetime >= current_timestamp THEN eventdatetime
    END,
    CASE
        WHEN E.status = 'created' AND eventdatetime < current_timestamp THEN eventdatetime
        WHEN E.status = 'finished' THEN eventdatetime
    END DESC

CodePudding user response：

try (because i have not done so meself):

order by
  CASE
    WHEN E.status = 'started' THEN 1
    WHEN E.status = 'created' && eventDateTime >= NOW() THEN 2
    WHEN E.status = 'created' && eventDateTime < NOW() THEN 3
    WHEN E.status = 'finished' THEN 4
    ELSE 5
  END,
  CASE
    WHEN E.status = 'started' THEN eventDateTime
    WHEN E.status = 'created' && eventDateTime >= NOW() THEN eventDateTime
    WHEN E.status = 'created' && eventDateTime < NOW() THEN -eventDateTime
    WHEN E.status = 'finished' THEN -eventDateTime
    ELSE eventDateTime 
  END

I might have put the - signs in the wrong place.... (sorry)

EDIT: After testing, because of the comments