SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Comparison help need
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

darms21
Yak Posting Veteran

54 Posts

Posted - 04/09/2013 :  12:17:49  Show Profile  Reply with Quote
Hi -
Table1:
Name Date Total
K 2/25/12 10
K 1/1/13 10
K 1/2/13 12
M 1/10/13 16
M 1/11/13 16

How can I create a statement that will identify all row where the Max date record for each Name has a greater total than the 2nd Max date?

I know that the following will give me the MAX date for each Name

SELECT Name, MAX(Date)
FROM Table1 
GROUP BY Name

Edited by - darms21 on 04/09/2013 12:28:07

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 04/09/2013 :  13:17:28  Show Profile  Visit russell's Homepage  Reply with Quote

WITH t AS (
	SELECT	name, date, total,
		row_number() over (partition by name order by date desc) ix
	FROM	Table1
)

SELECT	t1.name, t1.date, t1.total
FROM	t t1
JOIN	t t2
On	t1.name = t2.name
And	t1.ix = 1
And	t2.ix = 2
WHERE	t1.total > t2.total;

Edited by - russell on 04/09/2013 13:18:23
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000