Need to get first date in multiple column table

Question

I have a table with multiple date column, and i need to get the first date within all the column. I have a SQL query to get all the first date in column, but how can i get the first date within the result? The final result i need is just 1 first/earliest date within all column.

My Query:

SELECT
(SELECT EventDate1 FROM 33160_30006_feed WHERE EventDate1 !='' ORDER BY EventDate1 LIMIT 1) AS EventDate_1,
(SELECT EventDate2 FROM 33160_30006_feed WHERE EventDate2 !='' ORDER BY EventDate2 LIMIT 1) AS EventDate_2,
(SELECT EventDate3 FROM 33160_30006_feed WHERE EventDate3 !='' ORDER BY EventDate3 LIMIT 1) AS EventDate_3,
(SELECT EventDate4 FROM 33160_30006_feed WHERE EventDate4 !='' ORDER BY EventDate4 LIMIT 1) AS EventDate_4,
(SELECT EventDate5 FROM 33160_30006_feed WHERE EventDate5 !='' ORDER BY EventDate5 LIMIT 1) AS EventDate_5,
(SELECT EventDate6 FROM 33160_30006_feed WHERE EventDate6 !='' ORDER BY EventDate6 LIMIT 1) AS EventDate_6,
(SELECT EventDate7 FROM 33160_30006_feed WHERE EventDate7 !='' ORDER BY EventDate7 LIMIT 1) AS EventDate_7,
(SELECT EventDate8 FROM 33160_30006_feed WHERE EventDate8 !='' ORDER BY EventDate8 LIMIT 1) AS EventDate_8,
(SELECT EventDate9 FROM 33160_30006_feed WHERE EventDate9 !='' ORDER BY EventDate9 LIMIT 1) AS EventDate_9,
(SELECT EventDate10 FROM 33160_30006_feed WHERE EventDate10 !='' ORDER BY EventDate10 LIMIT 1) AS EventDate_10

My Result:

EventDate_1|EventDate_2|EventDate_3|EventDate_4|EventDate_5|EventDate_6|EventDate_7|EventDate_8|EventDate_9|EventDate_10|
 2015-06-01| 2015-03-25| 2015-03-27| 2015-03-26| 2015-03-24| 2015-05-08| 2015-03-25| 2015-03-25| 2015-03-29|  2015-03-25

score 2 · Answer 1 · edited May 23 '17 at 12:29

2

You could try GREATEST or LEAST

See other SO posts on this issue and see if that helps too.

edited May 23 '17 at 12:29

Community

1
1

answered Aug 07 '15 at 08:29

Mr Moose

5,946
7
34
69

Patrick · Accepted Answer · 2015-08-07T09:44:24.857

1

I would select the minimum over the union of the dates:

SELECT MIN(AllEventDates.EventDate)
FROM
(
    SELECT EventDate1 AS EventDate FROM 33160_30006_feed
    UNION
    SELECT EventDate2 AS EventDate FROM 33160_30006_feed
    UNION
    SELECT EventDateN AS EventDate FROM 33160_30006_feed
) AS AllEventDates
WHERE AllEventDates.EventDate != ''

edited Aug 07 '15 at 09:44

answered Aug 07 '15 at 08:35

Patrick

668
4
11

Patrick, i try with your query, but i get following error, what should i do? MySQL said: Documentation #1248 - Every derived table must have its own alias – Alex Loh Aug 07 '15 at 08:52
Adding an ` AS AllEventDates` after the last closing bracket will fix this. – rostbot Aug 07 '15 at 08:55
Move the AS AllEventDates out of the ) bracket and it work. Thanks rostbot and guys. – Alex Loh Aug 07 '15 at 09:01
Thanks, I fixed it in my answer ;-) – Patrick Aug 07 '15 at 09:04

Need to get first date in multiple column table

2 Answers2