SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Paging results - no ID column
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tech_1
Posting Yak Master

105 Posts

Posted - 02/15/2013 :  17:33:28  Show Profile  Reply with Quote
Hi.
I know how to do paging in SQL Server 2005/2008. Well, it's been a while if I am honest!

I will be passing into a SPROC some params including PageNumber and ItemsPerPage. I also will need to return a "total records" result via an OUTPUT parameter.

Now, I have a table which has no ID column. but thousands of records.
How can I create an efficient query which does the above? (Paging and also finding the total records)

Many thanks.

James K
Flowing Fount of Yak Knowledge

3587 Posts

Posted - 02/15/2013 :  18:44:57  Show Profile  Reply with Quote
All of that can be done, but you will need some way to order the rows, so the specific rows in a given page are deterministic. Do you have something like that even if you don't have a PK? Also, if the data changes (insertions/updates/deletes) while you are paging, without an ever-increasing PK, it would not be dependable.
Go to Top of Page

tech_1
Posting Yak Master

105 Posts

Posted - 02/15/2013 :  19:02:49  Show Profile  Reply with Quote
Just to add another spanner to the works... the query in question has a UNION. This brings back the complete resultset of results required for the search criteria

To go to your question James K, there is a unique record ID and it is set not within the DB but from the external apps.


usually I do something like this... without the union:

quote:

DECLARE @firstRow int
DECLARE @lastRow int
SELECT @firstRow = (@pageNumber - 1) * @itemsPerPage + 1,
@lastRow = (@pageNumber - 1) * @itemsPerPage + @itemsPerPage,
@totalRecords =
(SELECT COUNT(DISTINCT(t.[TrackID])) FROM Tracks t
INNER JOIN Artist a ON
a.ArtistID = t.ArtistID

INNER JOIN TrackOccasion tOcc ON
tOcc.TrackID = t.TrackID

WHERE a.ArtistName LIKE '%' + @keyword + '%'
AND tocc.OccasionID = @occasionID
);


WITH TracksQuery AS
(
SELECT a.ArtistName,
t.TrackID, t.TrackName, t.FullDuration, t.NonRegisteredPlaybackStartTime,
t.NonRegisteredPlaybackEndTime, t.RegisteredPlaybackStartTime,
t.RegisteredPlaybackEndTime, t.TrackDescription, t.TrackURL, t.GuidanceNotes, t.Religious, t.CatNo, t.Era, t.Tempo, t.Vocals,
t.[Year],
t.AdditionalInformation,
ROW_NUMBER() OVER (ORDER BY t.[TrackName], a.[ArtistName] ASC) AS RowNumber
FROM Tracks t

INNER JOIN Artist a ON
a.ArtistID = t.ArtistID

INNER JOIN TrackOccasion tOcc ON
tOcc.TrackID = t.TrackID

WHERE a.ArtistName LIKE '%' + @keyword + '%'
AND tocc.OccasionID = @occasionID
)

SELECT RowNumber, ArtistName,
TrackID, TrackName, FullDuration, NonRegisteredPlaybackStartTime,
NonRegisteredPlaybackEndTime, RegisteredPlaybackStartTime,
RegisteredPlaybackEndTime, TrackDescription, TrackURL, GuidanceNotes, Religious, CatNo, Era, Tempo, Vocals,
[Year], AdditionalInformation

FROM TracksQuery
WHERE RowNumber BETWEEN @firstRow AND @lastRow




but in this instance, it will be a union involvement.
Go to Top of Page

tech_1
Posting Yak Master

105 Posts

Posted - 02/15/2013 :  19:54:35  Show Profile  Reply with Quote
ok so I am almost there but with a problem. Not only is the performance hidious, but I guess it is because of the way the DB is and nothing I can really do about it but this does not quite work.

After the UNION, it brings back some more records.
So I am expecting just 10 items to show as per the paging request but it brings back 14 (the UNION adds the 4 records)

quote:

DECLARE @itemsPerPage int
SET @itemsPerPage = 10
DECLARE @pageNumber int
SET @pageNumber = 1
DECLARE @totalRecords int

DECLARE @firstRow int
DECLARE @lastRow int
SELECT @firstRow = (@pageNumber - 1) * @itemsPerPage + 1,
@lastRow = (@pageNumber - 1) * @itemsPerPage + @itemsPerPage,
@totalRecords =
(SELECT COUNT(tmpCount.ControlNumber) FROM (
SELECT DISTINCT <fieldListHere>
FROM Control
<inner join statements>
<Left outer join statement>
WHERE (<first condition>) AND Manufacturer.MfrName LIKE @manufacturer
UNION
SELECT DISTINCT <fieldListHere>
FROM Control
<inner join statements here... 1 extra table than above>
<Left outer join statement>
WHERE (<first condition>)
AND Manufacturer.MfrName LIKE @manufacturer

) AS tmpCount);

WITH AllMyRecords AS (
SELECT DISTINCT <fieldListHere>
,ROW_NUMBER() OVER
(ORDER BY <fields> ASC) AS RowNumber
FROM Control
inner join statements>
<Left outer join statement>
WHERE (<first condition>) AND Manufacturer.MfrName LIKE @manufacturer
UNION
SELECT DISTINCT <fieldListHere>,
ROW_NUMBER() OVER
(ORDER BY <fields> ASC) AS RowNumber
FROM Control
<inner join statements here... 1 extra table than above>
<Left outer join statement>
WHERE (<first condition>)
AND Manufacturer.MfrName LIKE @manufacturer
)


SELECT * FROM AllMyRecords WHERE RowNumber BETWEEN @firstRow AND @lastRow
ORDER BY AllMyRecords.MfrName, AllMyRecords.ModelNumber ASC


Edited by - tech_1 on 02/15/2013 19:58:42
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/16/2013 :  02:31:42  Show Profile  Reply with Quote
can you show some sample data and explain your problem. what additional data you're getting? and how your output should be
Without that its difficult for us to get your issue as we cant see your system

See here for guidelines on how to post data in consumable format

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.27 seconds. Powered By: Snitz Forums 2000