Select Top ..... - 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

General SQL Server Forums

New to SQL Server Programming

Select Top .....

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2012-10-24 : 06:37:05

I have a Stored procedure that selects the top 5.The results are displayed on asp.net webpage in a datagrid. Is there anyway I can select the top 5, then the next 5, then the next 5 etc etc - other than using paging in the datagrid. I would like to do it using the sp

SELECT TOP 5 CNo, Dt, Dn, St, COUNT(*) AS TotalFiles
FROM Mytable

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-10-24 : 06:40:20

Hi.

1) You need an order by. SELECT TOP X without an ORDER BY is not guaranteed.

Pagination is one of those ares that there doesn't seem to be a great way to do it in sql server natively.

Probably you could use something like this:
http://stackoverflow.com/questions/109232/what-is-the-best-way-to-paginate-results-in-sql-server

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

Pinto
Aged Yak Warrior

590 Posts

Posted - 2012-10-24 : 06:52:53

Sorry, there was an ORDER BY, I just included the part of the sp I thought was relevant

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-10-24 : 07:22:46

Which version? v2012 has an offset.
Otherwise you need to pass the page number and

declare @i int
select @i = 5 * pageno

select top 5 *
from(
SELECT TOP (@i) CNo, Dt, Dn, St, COUNT(*) AS TotalFiles
FROM Mytable
group by CNo, Dt, Dn, St
order by ...
) a
order by ... desc

You probably wouldn't want to do a database call for each 5 though.
Also think about what happens if the data changes between a page fetch - you might want to take a copy of the data to page through.

==========================================
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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources