CODEWITHSUNDEEP
mysqlsql
Jon Schlossberg
Jon Schlossberg

Reputation: 409

SQL query to sort multiple columns together

I need help writing an SQL query for mySQL 5.5.

I have a table with columns startDate, startTime, endDate, endTime.

The simple case:

SELECT * from Activity
ORDER BY startDate desc, startTime desc

Easy enough. But now I want to add in the endDate and endTime columns. I want the startDate and endDate columns to be mixed in together and then secondarily the startTime and endTime columns added in.

Some simple data:

startDate   startTime   endDate     endTime
2015-08-01  11:00       0000-00-00  00:00
2015-08-01  12:00       0000-00-00  00:00
2015-08-06  22:00       2015-08-07  09:00
2015-08-07  23:00       2015-08-08  08:00
2015-08-07  12:00       0000-00-00  00:00

When sorted should return:

startDate   startTime   endDate     endTime
2015-08-07  21:00       2015-08-08  08:00
2015-08-07  23:00       0000-00-00  00:00
2015-08-06  22:00       2015-08-07  09:00
2015-08-01  12:00       0000-00-00  00:00
2015-08-01  11:00       0000-00-00  00:00

First result's endDate is the largest. Second result's startTime > third result's endTime. Then it's just sorted based on startDate as the primary sort column and then startTime.

I looked into a CASE statement but can't get it right.

I know it's not the best db design. Any help would be great!

Thanks.

EDIT

Given the suggested SQL below, here are some results.

2015-09-02   07:16:00   0000-00-00   00:00:00
2015-09-01   19:47:00   0000-00-00   00:00:00
2015-09-01   19:47:00   0000-00-00   00:00:00
2015-09-01   16:33:00   0000-00-00   00:00:00   Row A
2015-09-01   14:51:00   0000-00-00   00:00:00
2015-09-01   12:05:00   0000-00-00   00:00:00
2015-09-01   12:01:00   0000-00-00   00:00:00
2015-09-01   12:00:00   0000-00-00   00:00:00
2015-09-01   11:45:00   0000-00-00   00:00:00   Row B
2015-09-01   08:09:00   0000-00-00   00:00:00
2015-08-31   23:00:00   2015-09-01   11:00:00   Row C
2015-08-31   17:27:00   0000-00-00   00:00:00
2015-08-31   14:57:00   2015-09-01   14:57:00   Row D

What I want is row C to be sorted just below row B. Also row D should be sorted just below row A.

Sorry I'm not being clear. I want startDate and startTime to be sorted Desc. But if a row has an endDate and endTime, then that row should be sorted using those values (and not startDate and startTime).

Thanks!

Upvotes: 2

Views: 103

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

I think you want:

order by greatest(addtime(endDate, endTime), addtime(startDate, startTime))

Upvotes: 0

Related Questions