Most recent records syntax help

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 2005 Forums

Transact-SQL (2005)

Most recent records syntax help

Author

Topic

eljapo4
Posting Yak Master

100 Posts

Posted - 2011-11-17 : 05:30:00

Hi I have the following code:


(
SELECT --use the row_number function to get the newest record
    ROW_NUMBER() OVER(PARTITION BY s.SpecRecordID_rc ORDER BY  s.SpecIssueNo DESC) AS seq
FROM dbo.mp_RPINSpecifications s (NOLOCK)
)
SELECT  s.SpecRecordID_rc,
	s.SpecIssueNo,
	s.SpecpacksPerMinute,
	r.RecordID,
	r.Resource,
	s.SpecID
FROM dbo.mp_RPINSpecifications s (NOLOCK)
INNER JOIN mp_rcResourceData r (NOLOCK) ON s.SpecRecordID_rc = r.RecordID
WHERE seq = 1 --filter for the newest record only
ORDER BY r.Resource,
         s.SpecIssueNo

I am trying to get the most recent s.SpecIssueNo for each Resource, if I run the code inside the brackets I can see the seq col being filled correctly but when I run the full query i am getting this error message: Msg 207, Level 16, State 1, Line 21
Invalid column name 'seq'.

Any ideas to help me would be great.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-17 : 05:55:56

with cte as
(
SELECT --use the row_number function to get the newest record
*, ROW_NUMBER() OVER(PARTITION BY s.SpecRecordID_rc ORDER BY s.SpecIssueNo DESC) AS seq
FROM dbo.mp_RPINSpecifications s (NOLOCK)
)
SELECT s.SpecRecordID_rc,
s.SpecIssueNo,
s.SpecpacksPerMinute,
r.RecordID,
r.Resource,
s.SpecID
FROM cte s
INNER JOIN mp_rcResourceData r (NOLOCK) ON s.SpecRecordID_rc = r.RecordID
WHERE seq = 1 --filter for the newest record only
ORDER BY r.Resource,
s.SpecIssueNo

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

eljapo4
Posting Yak Master

100 Posts

Posted - 2011-11-17 : 06:07:19

Nigel - thank you that worked!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources