September 21, 2009 at 11:22 am
Ok, I have the Monday morning blues. I can't think for the life of me how to do what I'm wanting. I want to select all fields from my table, where my eff_year = max. Basically, I have a field called LRSN and there might be 5 records with the same LRSN; however, there are going to be different eff_year for the the same LRSN and in my query, I only want the MAX year. So, I wrote this:
SELECT LRSN, MAX(EFF_YEAR) AS EFF_YEAR
FROM dbo.VALUATION
GROUP BY LRSN
ORDER BY LRSN
This works perfectly, only I get just two columns, whereas I want all columns. I know that I've done this before, but I just can't think today!
Please help!
Thanks,
Jordon
September 21, 2009 at 11:34 am
SELECT *
FROM dbo.VALUATION A
INNER JOIN (
SELECT LRSN, MAX(EFF_YEAR) AS EFF_YEAR
FROM dbo.VALUATION
GROUP BY LRSN
)B on A.LRSN=B.LRSN and A.EFF_YEAR=B.EFF_YEAR
HTH,
Supriya
September 21, 2009 at 11:47 am
Sometimes I just want to say "DUH" to myself!!! :crazy:
Thank you, that worked perfectly!!!
Jordon
September 21, 2009 at 6:39 pm
Another
WITH HighestYears AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY [LRSN] ORDER BY [EFF_YEAR] DESC) __rn
FROM dbo.VALUATION)
SELECT * FROM HighestYears WHERE __rn = 1
You'll get an extra column on the end - but it can be more efficient to use ROW_NUMBER sometimes. 🙂
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply