Selected no of rows for a sorted query

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

Transact-SQL (2008)

Selected no of rows for a sorted query

Author

Topic

Jawad
Starting Member

17 Posts

Posted - 2011-12-15 : 03:47:41

Hi

simple query

select * from mytable order by col1
I can have top n number of records as follows
select top n * from mytable order by col1

but i wish to parametrize the query or even a procedure to get sorted records with started row and last row on user choice i.e.

select <<from row 10 to row 20>> * from mytable order by col1

Wishes
J a w a d
J a w a d

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2011-12-15 : 04:28:45

Try this

Select * from (Select * from mytable)t where COL between 10 and 20 order by col

Senthil Kumar C
------------------------------------------------------
MCITP - Database Administration SQL SERVER 2008
MCTS - Database Development SQL SERVER 2008

sql-programmers
Posting Yak Master

190 Posts

Posted - 2011-12-15 : 04:53:37

Hi This Query may help you

WITH OrderedOrders AS
(
SELECT col1,
ROW_NUMBER() OVER (ORDER BY col1) AS 'RowNumber'
FROM mytable
)
SELECT *
FROM OrderedOrders
WHERE RowNumber BETWEEN 10 AND 20;

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

Jawad
Starting Member

17 Posts

Posted - 2011-12-15 : 22:27:47

Thanks sql-programmers

your solution is what i needed.

J a w a d

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-12-16 : 00:45:21

quote:
Originally posted by senthil_nagore

Try this

Select * from (Select * from mytable)t where COL between 10 and 20 order by col

Senthil Kumar C
------------------------------------------------------
MCITP - Database Administration SQL SERVER 2008
MCTS - Database Development SQL SERVER 2008

The derived table does not make any sense. It is same as

Select * from mytable where COL between 10 and 20 order by col

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-12-16 : 00:46:34

quote:
Originally posted by Jawad

Thanks sql-programmers

your solution is what i needed.

J a w a d

Also see what you can do with row_number() function
http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

Madhivanan

Failing to plan is Planning to fail

sql-programmers
Posting Yak Master

190 Posts

Posted - 2011-12-16 : 04:00:55

quote:
Originally posted by madhivanan

quote:
Originally posted by Jawad

Thanks sql-programmers

your solution is what i needed.

J a w a d

Also see what you can do with row_number() function
http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

Madhivanan

Failing to plan is Planning to fail

Thanks for your link. Its very useful.

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources