ROW_NUMBER() Problem - SQL Server Forums

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)

ROW_NUMBER() Problem

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2009-11-23 : 08:41:44

Hi,

I'm trying to obtain paged results from a large amount of data, I have this:

SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY [3year10knotmaintainedCH] ASC) AS Row, * FROM vwGenerateGrid2) As tbl WHERE manufacturer = 'audi' ORDER BY [3year10knotmaintainedCH] ASC

Ideally I wanted to change my order by to include WHERE row >=1 AND row <=10 for a page size of 10.

The problem is my row numbers are not sequential, they look like this

813
855
867
894
920
1024

etc

Obviously my syntax is wrong, can anyone help?

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-11-23 : 08:44:51

put the WHERE manufacturer = 'audi' inside the inner query.

Your row_number() is generating for the entire table vwGenerateGrid2 but your query only selected a subset of it

KH
[spoiler]Time is always against us[/spoiler]

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2009-11-23 : 10:09:57

That works great thanks. The only problem is the performance, it takes around 10 seconds to execute.

This is the query

SELECT TOP 50 * FROM (SELECT ROW_NUMBER() OVER (ORDER BY [3year10knotmaintainedCH] ASC) AS Row, * FROM vwGenerateGrid2) As tbl WHERE row >= 7301 AND row <= 7350 ORDER BY [3year10knotmaintainedCH] ASC

Is there anyway I can make it more efficient?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-23 : 10:13:11

quote:
Originally posted by Mondeo

That works great thanks. The only problem is the performance, it takes around 10 seconds to execute.

This is the query

SELECT TOP 50 * FROM (SELECT ROW_NUMBER() OVER (ORDER BY [3year10knotmaintainedCH] ASC) AS Row, * FROM vwGenerateGrid2) As tbl WHERE row >= 7301 AND row <= 7350 ORDER BY [3year10knotmaintainedCH] ASC

Is there anyway I can make it more efficient?

Thanks

do filetring first then apply numbering


SELECT TOP 50 * FROM (SELECT ROW_NUMBER() OVER (ORDER BY [3year10knotmaintainedCH] ASC) AS Row, * FROM vwGenerateGrid2 WHERE manufacturer = 'audi' ) As tbl ORDER BY [3year10knotmaintainedCH] ASC

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2009-11-23 : 10:17:58

Hi,

I didn't understand your example, where do I filter the row numbers?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-23 : 11:09:51

why filter on row numbers? as per your first post what you need is filter based on manufacturer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources