Using ROW_NUMBER in WHERE clause

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)

Using ROW_NUMBER in WHERE clause

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2009-04-02 : 06:29:45

Hi there

I have a table that contains a load of records. I want to select only the MAX(TranID) from the TOP (@x) amount. I created the following query:

SELECT @TransactionID = MAX(tranid) FROM dbo.test
where ROW_NUMBER() OVER (ORDER BY tranid) <= @BatchSize

But I get the error:
Msg 4108, Level 15, State 1, Procedure DeleteFromTest, Line 14
Windowed functions can only appear in the SELECT or ORDER BY clauses.

How else can I model the query to get the same results?

Thanks

Hearty head pats

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-02 : 06:35:34

Select @TransactionID = MAX(tranid) from
(
SELECT tranid,ROW_NUMBER() OVER (ORDER BY tranid) as Sno FROM dbo.test
) as t where Sno<= @BatchSize

Madhivanan

Failing to plan is Planning to fail