Ordering by date when there are 3 date columns

Question

I have two tables, CHECKINS and TRIPS.

I need to list entries in these 2 tables to be ordered with the most recent date at top.

Normally, I would simply use JOIN and than user ORDER BY date column.

But here is the challenge; there is one date column in CHECKINS, VenueCreatedAt, while there are two for TRIPS, TripDateStarted and TripDateEnded.

I will much appreciate if you can help me deal with it. Test address:

http://sqlfiddle.com/#!2/7f6055/33

Do you want the order based on the most recent of any of the date columns? Can you post your desired result of the query? — Taryn, Mar 12 '13 at 17:50
Not all but based on one of these 3 columns; `VenueCreatedAt`, `TripDateStarted`, `TripDateEnded` — Efe, Mar 12 '13 at 17:51
@Efe - Please show us how you want to see the result based on your sample. — PM 77-1, Mar 12 '13 at 17:53
@Efe: What do you mean by "one of these"? How do you know which one? What do you mean by "most recent date"? What do you want your result to look like? — gen_Eric, Mar 12 '13 at 17:58
Perhaps I should have wrote "all of these". I need entries ordered based on `VenueCreatedAt`, `TripDateStarted` and `TripDateEnded` dates. For example if there is an entry with `TripDateStarted` 3/10 and another entry with `VenueCreatedAt` 3/9, entry with TripDateStarted should come on the top. — Efe, Mar 12 '13 at 18:02
Does `ORDER BY VenueCreatedAt, TripDateStarted, TripDateEnded` work? — gen_Eric, Mar 12 '13 at 18:02
@Efe: How would you know which date to use? Doesn't each row have a value for each date? — gen_Eric, Mar 12 '13 at 18:05
@Efe: This doesn't make any sense. You have to know which date (from which row) you want to sort by. Can you give us an example of a few rows, and how'd you like them sorted. — gen_Eric, Mar 12 '13 at 18:17
What should happen when there are dates in more than one of these date fields? How do you want that handled? — Bafsky, Mar 12 '13 at 18:25
I dont know how else to explain it anymore. I will find a way to fix the error and share it here. — Efe, Mar 12 '13 at 23:02

score 3 · Answer 1 · edited May 23 '17 at 12:11

3

How about:

SELECT * FROM TRIPS LEFT JOIN CHECKINS 
USING (MemberID) ORDER BY CheckInDateEntered ASC;

(http://sqlfiddle.com/#!2/7f6055/3)

You can also order by multiple fields separating them by commas, in order of priority:

SELECT * FROM TRIPS LEFT JOIN CHECKINS USING (MemberID) 
ORDER BY VenueCreatedAt,TripDateStarted,TripDateEnded ASC;

(http://sqlfiddle.com/#!2/7f6055/10)

And if that doesn't work either, check out the LEAST function:

SELECT * FROM TRIPS LEFT JOIN CHECKINS USING (MemberID) 
ORDER BY LEAST(VenueCreatedAt,TripDateStarted,TripDateEnded) ASC;

(http://sqlfiddle.com/#!2/7f6055/15)

But be aware this will only sort by the one column with the lowest date. In case you want to sort by the minimum value on each row (alternating columns), maybe any of these three other questions may help you:

Order by max value in three different columns

Select Smallest Value From Multiple Columns with PHP/MySQL

Sorting a MySQL query with ORDER BY or with PHP sort functions

(they are all about sorting by the minimum or maximum value on each row of a particular set of columns)

edited May 23 '17 at 12:11

Community

1
1

answered Mar 12 '13 at 17:55

NotGaeL

8,344
5
40
70

This wont do it, you ordered them by `CheckInDateEntered` but I need to order them based on all the 3 columns; VenueCreatedAt, TripDateStarted, TripDateEnded – Efe Mar 12 '13 at 17:59
Sorry, I just saw that in the comments. I updated my answer, hope it helps :) – NotGaeL Mar 12 '13 at 18:04
I tried the examples you provided earlier but they are not working correctly just as on the test addresses that you sent. – Efe Mar 12 '13 at 23:02
With the all the examples you created, lots of data is in a loop. They are displayed repetitively. – Efe Mar 13 '13 at 21:03
I'm puzzled, I wish you could show me an example... Anyway, try using `DISTINCT` (http://dev.mysql.com/doc/refman/5.0/en/distinct-optimization.html) on your query, and it won't show the same row twice: `SELECT DISTINCT * FROM TRIPS LEFT JOIN CHECKINS USING (MemberID) ORDER BY LEAST(VenueCreatedAt,TripDateStarted,TripDateEnded) ASC;` (http://sqlfiddle.com/#!2/7f6055/39) – NotGaeL Mar 14 '13 at 14:36
I would like to list all the entries from 2 tables ordered by the date they were entered. BUT all your examples have 2 entries on each row. For example, in your last example, `TRIPSID = 12` is displayed for 11 times. – Efe Mar 14 '13 at 15:48
That's because one trip can have more than one venue (checkin). You can see the different checkin ids in the mentioned rows making the entry unique. Try something with GROUP BY TripsID, like `SELECT * FROM TRIPS LEFT JOIN CHECKINS USING (MemberID) GROUP BY TripsID ORDER BY LEAST(VenueCreatedAt,TripDateStarted,TripDateEnded) ASC` if you want to list just one venue per trip (sqlfiddle.com/#!2/7f6055/42). Also, try to specify your query before you try to code it. Work on the question before trying to find the answer: Once you know what you want it will be much easier to find out how to get it... – NotGaeL Mar 14 '13 at 17:08

Ordering by date when there are 3 date columns

1 Answers1