Comparison help need - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Comparison help need

Author

Topic

darms21
Yak Posting Veteran

54 Posts

Posted - 2013-04-09 : 12:17:49

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

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-04-09 : 13:17:28

[code]
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;[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources