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]
Go to Top of Page

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
Go to Top of Page

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/

Go to Top of Page

sync00
Starting Member

24 Posts

Posted - 2012-12-03 : 11:56:42
Very nice.

Thank you.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

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

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

Go to Top of Page
   

- Advertisement -