How to fetch specific set of records from a table

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

Old Forums

CLOSED - General SQL Server

How to fetch specific set of records from a table

Author

Topic

mohithmv
Starting Member

8 Posts

Posted - 2005-05-19 : 08:08:40

Hi All,

We have list page (ASP) displaying all users. We have introduced pagination concept over here. We have written stored Proc to

fetch the required number of records from the table.

As of now we are fetching the data in his manner:

n = page size * page number

Select top n from Users.

In this logic we are un-necessarly fetching
(page size * (page number - 1)) records as well. ie is the previous page records, but we are not displaying it in list

screen.

then we wanted to implement the following logic:

n = page size * page number
n1 = page size * (page number-1)

Select top n from Users.

minus

Select top n1 from Users.

Note: All data to fetched will be in sorted on user_id.

but we do not have "minus" in SQL Server 2000. and were unable to implement this logic.

can anyone help us out.

Thanks in advance.

Regards,
Mohith

nr
SQLTeam MVY

12543 Posts

Posted - 2005-05-19 : 12:56:58

for rec 21 to 30
select * from (select top 10 * from (select top 30 * from tbl order by fld) a order by fld dec) b order by fld

Better to do this in an SP and just pass the page number and page size.

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

SreenivasBora
Posting Yak Master

164 Posts

Posted - 2005-05-19 : 18:15:53

create a employee table call emp (Eno integer identity(1,1), ename varchar(25), dob datetime default getdate())

declare @i Integer
set @i = 0

While @i < 101
Begin
Insert into Emp(Ename) values ('Test data' + str(@i))
Set @i = @i + 1
End

It will creates 100 records for you.

Now I want to display 10 records per page in my grid. and next page 11-20 and 3rd page 21- 30 etc.

First 10 records:
=================

Select top 10 * from emp as A
Where A.eno in (select B.eno from emp as b Where b.eno not in (select TOP 0 Eno from emp))
Order by A.eno

Second 10 records set (not between 1 - 10):
============================================

Select top 10 * from emp as A
Where A.eno in (select B.eno from emp as b Where b.eno not in (select TOP 2 * 10 Eno from emp))
Order by A.eno

nth 10 records set (not in n-10 records):
==========================================

Select top 10 * from emp as A
Where A.eno in (select B.eno from emp as b Where b.eno not in (select TOP N * 10 Eno from emp))
Order by A.eno

Hope this will resolve your problem.

With Regards
Sreenivas Reddy B

mohithmv
Starting Member

8 Posts

Posted - 2005-05-20 : 01:19:35

Hi nr & Sreenivas,

Thanks a LOT for all your help.

Its working fantastic.

Regards,
Mohith

raclede
Posting Yak Master

180 Posts

Posted - 2005-05-20 : 01:36:31

sample SP that works... to call it just EXEC SampleProc (PageNum, NumberOfRecordsPerPage, SearchParam)
CREATE PROCEDURE SampleProc(
@PageNum INT = 1,
@RecCntPerPage INT = 10,
@Param nvarchar(250)
)

AS
SET NOCOUNT ON
--************************* DECLARATION ************************************

DECLARE @strPageNum NVARCHAR(50)
DECLARE @strPageSize NVARCHAR(50)
DECLARE @strSkippedRows NVARCHAR(50)
DECLARE @intRecordcount INT
DECLARE @intPagecount INT
DECLARE @sortBy NVARCHAR(25)
DECLARE @strFilter NVARCHAR(4000)
DECLARE @strFields NVARCHAR(1000)
DECLARE @strTables NVARCHAR(1000)

IF @PageNum < 1 SET @PageNum = 1

SET @strPageNum = CONVERT(NVARCHAR(50), @PageNum)
SET @strPageSize = CONVERT(NVARCHAR(50), @RecCntPerPage)
SET @strSkippedRows = CONVERT(NVARCHAR(50), @RecCntPerPage * (@PageNum - 1))
SET @strTables = 'SubDistList' --- it is a view......

SET @SortBy = ' ORDER by SUBName asc '
SET @strFields = ' * '
SET @strFilter = ''

IF (@param IS NOT NULL AND @param != '')
BEGIN
SET @strFilter = @strFilter + ' WHERE (SUBNAME like ''' + CONVERT(NVARCHAR(250), @param) + '%'' OR CUST like ''' + CONVERT(NVARCHAR(250), @param) + '%'' OR SUB like ''' + CONVERT(NVARCHAR(15), @param) + '%'')'
END

--------------------------------------------- BEGIN PAGINATION ---------------------------------------------------------------------------

--START THE QUERY
IF @PageNum = 1 -- In this case we can execute a more efficient
BEGIN
-- query with no subqueries.
EXEC ( 'SELECT TOP ' + @strPageSize + ' (SELECT COUNT(*) FROM ' + @strTables + @strFilter + ') AS RECORDCOUNT, ' + @strFields + ' FROM ' + @strTables + ' ' + @strFilter + @SortBy)

END
ELSE -- Execute a structure of subqueries that brings the correct page.
BEGIN
IF (@strFilter='')
EXEC ('SELECT (SELECT COUNT(*) FROM ' + @strTables + ') AS RECORDCOUNT, ' + @strFields + ' FROM ' + @strTables + ' WHERE [sub] IN ' + '
(SELECT TOP ' + @strPageSize + ' [sub] FROM ' + @strTables + ' WHERE [sub] NOT IN ' + '
(SELECT TOP ' + @strSkippedRows + ' [sub] FROM ' + @strTables + @SortBy + ') '
+ @SortBy + ') ' + @SortBy
)
ELSE
EXEC ('SELECT (SELECT COUNT(*) FROM ' + @strTables + @strFilter + ') AS RECORDCOUNT, ' + @strFields + ' FROM ' + @strTables + ' WHERE [sub] IN ' + '
(SELECT TOP ' + @strPageSize + ' [sub] FROM ' + @strTables + @strFilter + ' AND [sub] NOT IN ' + '
(SELECT TOP ' + @strSkippedRows + ' [sub] FROM ' + @strTables + @strFilter + @SortBy + ') '
+ @SortBy + ') ' + @SortBy
)
END
GO

note: change [sub] to unique key in your table....

Cursors are for those who doesn't know how to use SQL
K.I.S.S. - Keep it simple stupid
raclede™

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources