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.
| 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/
|
|
|
|
|
|
|
Need help with query