2

I have a tableA of date ranges:

tranid    item    startdate     enddate
---------------------------------------
1          A      1/1/2000      2/2/2005
2          A      5/1/2000      2/2/2005
3          B      7/8/2015      9/8/2015
4          C      4/10/2007     7/20/2008
5          C      4/10/2003     7/20/2005

How to write a SQL query to only select the most recent transactions (ex the most recent start and end dates)?

For example, for A, the most recent date range is 5/1/2000 to 2/2/2005 and for C, the most recent date range is C 4/10/2007 to 7/20/2008.

I am at a complete loss to write this because it seems easy but is not. 

select item, max(enddate), max(startdate)
from tableA 
where max(enddate)
group by item, enddate, startdate

SQL Server returned an error related to 'having' on something like that - a problem with aggregates.

gracias :)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
holy_guacamole
  • 47
  • 1
  • 6
  • 5
    what if one row has a maximum start date and another has a maximum end date? – Vamsi Prabhala Sep 29 '16 at 01:05
  • Possible duplicate of [SQL Server: SELECT only the rows with MAX(DATE)](https://stackoverflow.com/questions/7118170/sql-server-select-only-the-rows-with-maxdate) – Vadzim Nov 02 '17 at 18:03

3 Answers3

2

You have a where max(enddate) which doesn't make sense, also you don't need to include startdate/enddate in your group by, just the item, so try:

select item, max(enddate), max(startdate)
from tableA
group by item
artm
  • 8,554
  • 3
  • 26
  • 43
0

You need ROW_NUMBER window function

:with cte as
(
Select Row_Number() Over(Partition by item order by startdate desc) RN,*
From yourtable 
)
Select * 
From cte 
Where RN = 1
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
0

This is a classical arg-max problem: where you want rows with most recent start date.

You need to split the problem into two steps:

  1. calculate the most recent start date for each item.
  2. join the maximum result with the original table to obtain the desirable rows.

Solution:

SELECT b.tranid, b.item, a.maxstartdate, b.enddate
FROM
  (SELECT t.item, MAX(t.startdate) maxstartdate
   FROM t
   GROUP BY t.item) a
JOIN t b
ON a.maxstartdate = b.startdate AND a.item = b.item;

Hope this can help you! :)

Quote
  • 41
  • 3