max sql question

qbjgqbjg
qbjgqbjg used Ask the Experts™
on
I have a table with acct numbers and descriptions and the acct yr. The descriptions have changed for 2012. I want to create a table variable that I can link to in a query that will have 1 record for each account number with the description for the most recent year. Not all account numbers will be present in 2012. I want to use it to make sure I always retrieve the most recent description even when I have an older year. I am including the code that I tried. It pulls all of the years not just the max. MAX-acct.txt
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Take ACCT_YEAR our of your group by.

Greg

qbjgqbjgConsultant

Author

Commented:
That still pulls multiples for each account. I think I have the answer. max-in-where-in-subquery.sql
Sr. System Analyst
Commented:
no need for temp table :)

select * from (
 select *, row_number() over (partititon by ACCT_NO order by ACCT_YEAR desc) rn
 from ESGACTTR
) x where rn=1
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

qbjgqbjgConsultant

Author

Commented:
I don't really need the order by.
HainKurtSr. System Analyst

Commented:
"I don't really need the order by"

is this for my post? to get the most recent, we need to sort it :) just try the query...
qbjgqbjgConsultant

Author

Commented:
Thanks
qbjgqbjgConsultant

Author

Commented:
this is what I actually used. It works great. I will need to use it in several different places, so I wanted the code for the table variable that I can use in several different scenarios.

DECLARE @Data TABLE
 (
 ACCT_NO char(44),
 ACCT_ID int,
 ACCT_TITLE char(40),
 ACCT_YEAR smallint,
 TITLE_1 char(40)
 )
INSERT INTO @Data
SELECT distinct
 a.ACCT_NO            as ACCT_NO,
 a.ACCT_ID             as ACCT_ID,
 a.ACCT_TITLE            as ACCT_TITLE,
 a.ACCT_YEAR            as ACCT_YEAR,
 a.TITLE_1            as  TITLE_1


FROM ESGACTTR a
WHERE TITLE_ID = -1
 and ACCT_YEAR =
   (SELECT max(ACCT_YEAR)
    FROM ESGACTTR b where a.ACCT_ID = ACCT_ID and TITLE_ID = -1)

ORDER BY
ACCT_ID,
ACCT_YEAR DESC

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial