CODEWITHSUNDEEP
sqlsql-servert-sqlgreatest-n-per-group
Colin O'Brien
Colin O'Brien

Reputation: 2325

T-SQL: How to select rows based on the max date?

I have a table which has two fields which I want to make a decision on:

- DATE
- DATE_LOADED

I want to know if there is a DATE (which may have many associated lines), say '2016-06-15', which has two unique DATE_LOADED entries, '2016-06-16' and '2016-06-17', then take the MAX DATE_LOADED lines only.

In real terms, two reports have been reporting overlapping data for the same day. The later report is a correction. All columns may have updated but DATE.

ASK:
For each DATE (not just each individual line), check if there is more than one DATE_LOADED, if there is then take all lines associated with that DATE and where DATE_LOADED = MAX DATE_LOADED for that particular date.

Upvotes: 2

Views: 6851

Answers (4)

Igor
Igor

Reputation: 62308

This query will get you a list of DATE, the total number of different DATE_LOADED values for that date, and the maximum DATE_LOADED value across those records.

SELECT DATE
   , COUNT(DISTINCT DATE_LOADED) AS [Total Different DATE_LOADED Values]
   , MAX(DATE_LOADED) AS [Max DATE_LOADED]
FROM YOURTABLE
GROUP BY DATE
HAVING COUNT(DISTINCT DATE_LOADED) > 1

I saw this later on

then take all lines associated with that DATE and where DATE_LOADED = MAX DATE_LOADED for that particular date.

This can be accomplished by joining the query above back to your table. This will yield a result of all records in your original table matching on the previous query.

SELECT yt.* 
FROM YOURTABLE AS yt INNER JOIN
    (SELECT DATE
       , MAX(DATE_LOADED) AS [Max DATE_LOADED]
    FROM YOURTABLE
    GROUP BY DATE
    HAVING COUNT(DISTINCT DATE_LOADED) > 1
    ) AS subQry ON yt.DATE = subQry.DATE AND yt.DATE_LOADED = subQry.[Max DATE_LOADED]

Upvotes: 3

user330315
user330315

Reputation:

This can be done using window functions

select *
from (
   select t.*, 
          row_number() over (partition by date order by date_loaded desc) as rn
   from the_table t
) x
where rn = 1;

If there are multiple rows with the same max date, the above would only return one of them. If you want all, you can use something like this:

select *
from (
   select t.*, 
          max(date_loaded) over (partition by date) as max_date_loaded
   from the_table t
) x
where date_loaded = max_date_loaded;

If you want to filter out DATEs with just a single DATE_LOADED, you can enhance the query to:

select *
from (
   select t.*, 
          max(date_loaded) over (partition by date) as max_date_loaded,
          count(*) over (partition by date) as cnt
   from the_table t
) x
where date_loaded = max_date_loaded
  and cnt > 1;

Upvotes: 4

gofr1
gofr1

Reputation: 15997

You can use CROSS APPLY or INNER JOIN for this purpose. Below is an example with CROSS APPLY.

SELECT  t.[DATE],
        t.DATE_LOADED    
FROM YourTable t
CROSS APPLY (
    SELECT MAX(DATE_LOADED) as MAX_DATE_LOADED 
    FROM YourTable 
    WHERE [DATE] = t.[DATE]) as p
WHERE t.DATE_LOADED = p.MAX_DATE_LOADED

For example if in your table there is something like:

DATE        DATE_LOADED
2016-06-15  2016-06-16
2016-06-15  2016-06-17
2016-06-15  2016-06-18
2016-06-15  2016-06-18

Above query will give you:

DATE        DATE_LOADED
2016-06-15  2016-06-18
2016-06-15  2016-06-18

Upvotes: 1

huMpty duMpty
huMpty duMpty

Reputation: 14470

I believe you are after something like below

Select Date,MAX(DateLoaded)
From TableName    
Group By Date

Upvotes: 0

Related Questions