Need help with query - 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 2000 Forums

SQL Server Development (2000)

Need help with query

Author

Topic

sync00
Starting Member

24 Posts

Posted - 2012-12-03 : 11:11:33

Here is some sample data.


PersonId  Year  Amount
23        2012   5
23        2011  10
25        2011  20
25        2011   6

I need to select the most recent Year and associate amount for each PersonId where the Amount is not 0.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-03 : 11:21:03

[code]SELECT
PersonId,
MAX([Year]) AS MaxYear
FROM
tbl
WHERE
Amount <> 0
GROUP BY
PersonId[/code]

sync00
Starting Member

24 Posts

Posted - 2012-12-03 : 11:34:40

Thanks. I forgot to mention that I need to return the Amount associated with the most recent year. That is the part I'm stuck on

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-03 : 11:40:33

[code]
SELECT t.*
FROM Table t
INNER JOIN (SELECT
PersonId,
MAX([Year]) AS MaxYear
FROM
tbl
WHERE
Amount <> 0
GROUP BY
PersonId
)t1
ON t1.PersonId = t.PersonId
AND t1.MaxYear = t.Year
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sync00
Starting Member

24 Posts

Posted - 2012-12-03 : 11:56:42

Very nice.

Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-03 : 12:15:19

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources