CODEWITHSUNDEEP
mysqlsql
ScottMcGready
ScottMcGready

Reputation: 1622

MySQL sort records by two alternating static values

I have a table full of data like so:

ID      datetime            ip_address      marker
289055  2016-11-17 17:54:51 XX.XXX.XXX.XXX  End
289037  2016-11-17 17:54:51 XX.XXX.XXX.XXX  Start
289057  2016-11-17 17:58:56 XX.XXX.XXX.XXX  Start
289123  2016-11-17 17:59:18 XX.XXX.XXX.XXX  End
289184  2016-11-17 18:05:55 XX.XXX.XXX.XXX  End
289125  2016-11-17 18:05:55 XX.XXX.XXX.XXX  Start
289186  2016-11-17 18:12:22 XX.XXX.XXX.XXX  Start
289292  2016-11-17 18:18:44 XX.XXX.XXX.XXX  End

I'm trying to sort the data by two values ("Start", "End") but "banded". What I mean by this is as follows:

ID      datetime            ip_address      marker
289037  2016-11-17 17:54:51 XX.XXX.XXX.XXX  Start
289055  2016-11-17 17:54:51 XX.XXX.XXX.XXX  End
289057  2016-11-17 17:58:56 XX.XXX.XXX.XXX  Start
289123  2016-11-17 17:59:18 XX.XXX.XXX.XXX  End
289125  2016-11-17 18:05:55 XX.XXX.XXX.XXX  Start
289184  2016-11-17 18:05:55 XX.XXX.XXX.XXX  End
289186  2016-11-17 18:12:22 XX.XXX.XXX.XXX  Start
289292  2016-11-17 18:18:44 XX.XXX.XXX.XXX  End

Notice how the marker column pivots between Start and End (start always being first and End always being last). I believe what I want to accomplish is possible but I'm not getting how to manipulate this data into what I want. I've tried a few different queries grouping or ordering by various columns however it always ends up either like the first table, or all records with "Start" as the marker grouped together, followed by "End".

Upvotes: 0

Views: 66

Answers (2)

FanoFN
FanoFN

Reputation: 7124

May I suggest an addition into your SELECT... to use in your ordering. For example:

SELECT ID,datetime,ip_address,marker,
CASE WHEN marker='Start' THEN 1 WHEN marker='End' THEN 2 END AS 'mcheck'
FROM table
ORDER BY datetime,ipaddress,mcheck;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270713

In MySQL 8+, this is easy:

order by row_number() over (partition by marker order by datetime),
         marker desc

This is much more challenging in earlier versions of MySQL.

Upvotes: 2

Related Questions