-1

This is very different from doing an SQL order by 2 date columns (or for proper way to sort sql columns, which is only for 1 column). There, we would do something like:

ORDER BY CASE WHEN date_1 > date_2 
         THEN date_2 ELSE date_1 END

FYI, I'm using YYY-MM-DD in this example for brevity, but I also need it to work for TIMESTAMP (YYYY-MM-DD HH:MI:SS)

I have this table:

id name date_1 date_2 date_3 date_4 date_5 date_6 date_7 date_8
1 John 2008-08-11 2008-08-12 2009-08-11 2009-08-21 2009-09-11 2017-08-11 2017-09-12 2017-09-30
2 Bill 2008-09-12 2008-09-12 2008-10-12 2011-09-12 2008-09-13 2022-05-20 2022-05-21 2022-05-22
3 Andy 2008-10-13 2008-10-13 2008-10-14 2008-10-15 2008-11-01 2008-11-02 2008-11-03 2008-11-04
4 Hank 2008-11-14 2008-11-15 2008-11-16 2008-11-17 2008-12-31 2009-01-01 2009-01-02 2009-01-02
5 Alex 2008-12-15 2018-12-15 2018-12-15 2018-12-16 2018-12-17 2018-12-18 2018-12-25 2008-12-31

... But, the permutations of that give me a headache, just to think about them.

This Answer had more of a "general solution", but that was to SELECT, not to ORDER BY...

SELECT MAX(date_col)
FROM(
  SELECT MAX(date_col1) AS date_col FROM some_table
  UNION
  SELECT MAX(date_col2) AS date_col FROM some_table
  UNION
  SELECT MAX(date_col3) AS date_col FROM some_table
  ...
)

Is there something more like that, such as could be created by iterating a loop in, say PHP or Node.js? I need something a scalable solution.

  • I only need to list each row once.
  • I want to order them each by whichever col has the most recent date of those I list on that row.

Something like:

SELECT * FROM some_table WHERE
(
  GREATEST OF date_1
  OR date_2
  OR date_3
  OR date_4
  OR date_5
  OR date_6
  OR date_7
  OR date_8
)
David דודו Markovitz
  • 42,900
  • 6
  • 64
  • 88
Jesse
  • 750
  • 1
  • 9
  • 25

2 Answers2

4

You can use the GREATEST function to achieve it.

SELECT  GREATEST(date_1,date_2,date_3,date_4,date_5,date_6,date_7,date_8) max_date,t.*
FROM Tab t
ORDER BY GREATEST(date_1,date_2,date_3,date_4,date_5,date_6,date_7,date_8) Desc;

DB Fiddle: Try it here

max_date id name date_1 date_2 date_3 date_4 date_5 date_6 date_7 date_8
2022-05-22 2 Bill 2008-09-12 2008-09-12 2008-10-12 2011-09-12 2008-09-13 2022-05-20 2022-05-21 2022-05-22
2018-12-25 5 Alex 2008-12-15 2018-12-15 2018-12-15 2018-12-16 2018-12-17 2018-12-18 2018-12-25 2008-12-31
2017-09-30 1 John 2008-08-11 2008-08-12 2009-08-11 2009-08-21 2009-09-11 2017-08-11 2017-09-12 2017-09-30
2009-01-02 4 Hank 2008-11-14 2008-11-15 2008-11-16 2008-11-17 2008-12-31 2009-01-01 2009-01-02 2009-01-02
2008-11-04 3 Andy 2008-10-13 2008-10-13 2008-10-14 2008-10-15 2008-11-01 2008-11-02 2008-11-03 2008-11-04
Abinash
  • 554
  • 2
  • 6
  • Could you mention that `GREATEST` is for mariadb? And, I'd welcome another Answer from you or anyone that can work with MySQL. – Jesse May 22 '22 at 15:24
  • 1
    `GREATEST` works in MySQL too. [Fiddle](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=764b61bf66f637603ea13316fb301f5d) – Abinash May 22 '22 at 15:27
  • 1
    If you decided that you want the `greatest` expression in the `select` clause, there is no need to duplicate it for the `order by` clause. `SELECT GREATEST(date_1,date_2,date_3,date_4,date_5,date_6,date_7,date_8) max_date,t.* FROM Tab t ORDER BY max_date Desc;` – David דודו Markovitz May 22 '22 at 17:31
  • @DavidדודוMarkovitz Thanks! I'd love an Answer to vote on. – Jesse May 22 '22 at 17:35
1

In the event of a NULL value, GREATEST could throw-off the ORDER.

Based on this Answer from a Question about GREATEST handling NULL, this would apply these tables, based on the approved Answer:

SELECT COALESCE (
  GREATEST(date_1,date_2,date_3,date_4,date_5,date_6,date_7,date_8),
  date_1,date_2,date_3,date_4,date_5,date_6,date_7,date_8 
) max_date,t.*
FROM TAB t
ORDER BY COALESCE (
  GREATEST(date_1,date_2,date_3,date_4,date_5,date_6,date_7,date_8),
  date_1,date_2,date_3,date_4,date_5,date_6,date_7,date_8
) DESC;
Jesse
  • 750
  • 1
  • 9
  • 25