Sql query needs to sort on multiple date columns together

Question

I have a table with three date fields, a start date, mid-term date, and end date. I would like to create a single query to get the most recent activity from the table. Activity in this case being when the date fields are updated.

Without having to write 3 separate queries and then combining the values in my code to get the 10 most recent activities, can I do this in one query. So right now I have

    SELECT TOP 10 * FROM core_table 
    ORDER BY [start_date] Desc

    SELECT TOP 10 * FROM core_table 
    ORDER BY [process_date] Desc

    SELECT TOP 10 * FROM core_table 
    ORDER BY [archive_date] Desc

So I would want to pull the results of those three queries together to get the top 10 entries based on all three dates.

looking for something like this?http://stackoverflow.com/questions/71022/sql-max-of-multiple-columns — ltiong_sh, May 12 '12 at 23:32
That I think gets the top row for each row, not a collection of rows, but helpful anyways. thanks! — Justin C, May 12 '12 at 23:35
I think Sven's solution from that post should work for you since it effectively calculates the max date for each row. You can then sort and filter on this calculated column: SELECT [other fields,] (SELECT Max(v) FROM (VALUES (date1),(date2),(date3),...) AS value(v)) as [MaxDate], FROM [YourTable] — Mike Parkhill, May 12 '12 at 23:41
@Mike - Thanks, I got the answer I need. If people think this is a duplicate I'm fine with it being closed, got what I need! — Justin C, May 12 '12 at 23:44

score 10 · Accepted Answer · edited May 13 '12 at 00:02

10

based on answer given by Itiong_sh, which is not exactly the same : you can do it in ORDER BY

select top 10 * from core_table
order by
  CASE
      WHEN start_date >= process_date AND start_date >= archive_date 
          THEN  start_date
      WHEN process_date >= archive_date 
          THEN  process_date
      ELSE  archive_date
  END
 DESC

edited May 13 '12 at 00:02

ypercubeᵀᴹ

113,259
19
174
235

answered May 12 '12 at 23:35

Raphaël Althaus

59,727
6
96
122

That's it, thanks! I added DESC after the END to get the dates in the order I wanted, but that takes care of the multiple date fields! – Justin C May 12 '12 at 23:42

ypercubeᵀᴹ · Answer 2 · 2012-05-13T00:14:49.990

2

I think you need UNION:

SELECT TOP 10               
    *
FROM
    (   ( SELECT TOP 10 
              *, start_date    AS activity_date
          FROM core_table 
          ORDER BY [start_date] DESC
        ) 
    UNION
        ( SELECT TOP 10 
              *, process_date  AS activity_date
          FROM core_table 
          ORDER BY [process_date] DESC
        ) 
    UNION
        ( SELECT TOP 10 
              *, archive_date  AS activity_date
          FROM core_table 
          ORDER BY [archive_date] DESC
        )
    ) AS t
ORDER BY activity_date DESC ;

edited May 13 '12 at 00:14

answered May 12 '12 at 23:43

ypercubeᵀᴹ

113,259
19
174
235

That was one of my first thoughts but I'm getting incorrect syntax, ORDER BY clauses have to be outside of the UNION. Not sure if that is a T-Sql thing or just SQL in general. – Justin C May 12 '12 at 23:45
That works, thanks. I'm sticking with Raphael's solutions b/c it has the side benefit of being in order by the combined dates. Your solution gives the top 10+ rows but orders them by the Primary Key column. – Justin C May 12 '12 at 23:53
See my edit. You can choose how many of the (30) rows you want changing the TOP 10 in the first line of the code. – ypercubeᵀᴹ May 12 '12 at 23:56
1

@ypercube I don't think this is correct. The `UNION` will compare all fields, including `activity_date`, which can have different values for the _same_ table row (since it will take its value from different fields). You'll end-up with unwanted duplicates. – Branko Dimitrijevic May 13 '12 at 01:55
@Branko: You are right off course. Then a modification of the external query is needed (unless the OP wants exactly this, a row to appear more than once if it has been both processed and updated recently) – ypercubeᵀᴹ May 13 '12 at 08:52

score 2 · Answer 3 · edited May 23 '17 at 12:08

An expansion on Raphaël Althaus' answer:

CREATE TABLE core_table (
    ...
    max_date AS
        CASE
            WHEN start_date >= process_date AND start_date >= archive_date
                THEN start_date
            WHEN process_date >= archive_date
                THEN process_date
            ELSE archive_date
        END
);

CREATE INDEX core_table_ie1 ON core_table (max_date);

Then, you can simply...

SELECT TOP 10 *
FROM core_table
ORDER BY max_date DESC;

...and it should use the index range scan instead of a full table scan.

Sql query needs to sort on multiple date columns together

3 Answers3