Select max id and date

Question

Hi Experts ,Please Help me with Below Requirement

SELECT 1 AS PRODUCD_id ,100 CASE_NUMBER,'2012-02-13' AS CASE_DATE
uNION ALL
SELECT 2 AS PRODUCD_id ,100 CASE_NUMBER,'2012-02-15' AS CASE_DATE
uNION ALL
SELECT 3 AS PRODUCD_id ,100 CASE_NUMBER,'2012-09-01' AS CASE_DATE
uNION ALL
SELECT 4 AS PRODUCD_id ,100 CASE_NUMBER,'2012-03-31' AS CASE_DATE
uNION ALL

SELECT 5 AS PRODUCD_id ,101 CASE_NUMBER,'2012-01-15' AS CASE_DATE
uNION ALL
SELECT 6 AS PRODUCD_id ,101 CASE_NUMBER,'2012-01-18' AS CASE_DATE
uNION ALL
SELECT 7 AS PRODUCD_id ,101 CASE_NUMBER,'2012-05-13' AS CASE_DATE
uNION ALL
SELECT 8 AS PRODUCD_id ,101 CASE_NUMBER,'2012-06-11' AS CASE_DATE
uNION ALL

SELECT 9 AS PRODUCD_id ,102 CASE_NUMBER,'2012-12-18' AS CASE_DATE
uNION ALL
SELECT 10 AS PRODUCD_id ,102 CASE_NUMBER,'2012-02-26' AS CASE_DATE
uNION ALL
SELECT 11 AS PRODUCD_id ,102 CASE_NUMBER,'2012-10-13' AS CASE_DATE
uNION ALL

I NEED PRODUCT_ID,CASE_NUMBER AND CASE_DATE WHERE CASE_DATE IS MAX FOR CASE_NUMBER
OUTPUT SHOULD BE

SELECT 3 AS PRODUCD_id ,100 CASE_NUMBER,'2012-09-01' AS CASE_DATE
uNION ALL
SELECT 8 AS PRODUCD_id ,101 CASE_NUMBER,'2012-06-11' AS CASE_DATE
uNION ALL
SELECT 9 AS PRODUCD_id ,102 CASE_NUMBER,'2012-12-18' AS CASE_DATE

Answer 1

You can do this using the ROW_NUMBER() window function:

;with cte as
(
	Select	PRODUCT_ID
			,CASE_NUMBER
			,CASE_DATE
			,ROW_NUMBER() OVER(Partition By CASE_NUMBER Order By CASE_DATE Desc) as RowNum
	From	@tvTable
)
Select	PRODUCT_ID
		,CASE_NUMBER
		,CASE_DATE
From	cte
Where	RowNum = 1

MSDN Page for ROW_NUMBER(): http://msdn.microsoft.com/en-us/library/ms186734.aspx

Answer 2

Johnson's solution will work adding a condition where RowNum=1.

;with cte as
(
	Select	PRODUCT_ID
			,CASE_NUMBER
			,CASE_DATE
			,ROW_NUMBER() OVER(Partition By CASE_NUMBER Order By CASE_DATE Desc) as RowNum
	From	@tvTable
)
Select	PRODUCT_ID
		,CASE_NUMBER
		,CASE_DATE
From	cte
Where RowNum=1

---

Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 3

Onother way is

declare @t table(product_id int,case_number int,case_date date)
insert into @t values(1,100,'2012-02-13'),(2,100,'2012-02-15')
,(3,100,'2012-09-01'),(4,100,'2012-03-31'),(5,101,'2012-01-15')
,(6,101,'2012-01-18'),(7,101,'2012-05-13'),(8,101,'2012-06-11')
,(9,102,'2012-12-18'),(10,102,'2012-02-26'),(11,102,'2012-10-13');
select t.*
from
(select MAX(case_date) as case_date 
from @t group by case_number) as t1
join @t as t on t.case_date = t1.case_date 
RESULTS:
product_id	case_number	case_date
3		100	2012-09-01
8		101	2012-06-11
9		102	2012-12-18

ESHANI. Please click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you