Selecting certain records... - 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 2000 Forums

Transact-SQL (2000)

Selecting certain records...

Author

Topic

Harry C
Posting Yak Master

148 Posts

Posted - 2005-02-07 : 13:50:03

ok, I know this has to be an easy one, so I apologize ahead of time. I have a sp that returns a records. I am trying to implement paging, so, I want to return only the records I want...

So, if I have 20 records total, but want to display 5 at a time, first I want to return 1 to 5, when NExt is clicked, 6 to 10, and so on. I have seen some solutions, but most assume that you have a sequential value in your Table. I have a pk, but the values of the PK could be 1,2,3,4,28...so selectin records with ID 1 to 5 wouldn't work in that case...How can I accomplish this? Thanks

SELECT KnowledgeBase.URL,
KnowledgeBase.KnowledgeBaseID,
ContentType.ContentTypeDisplayName,
KnowledgeBase.Keywords
FROM CourseAddendum INNER JOIN
KnowledgeBase ON CourseAddendum.KnowledgeBaseID = KnowledgeBase.KnowledgeBaseID INNER JOIN
ContentType ON KnowledgeBase.ContentTypeID = ContentType.ContentTypeID
WHERE (CourseAddendum.CourseID = @CourseID)

chadmat
The Chadinator

1974 Posts

Posted - 2005-02-07 : 14:42:21

Would you still order them by the PK? There has to be something to order them by for paging to make sense.

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-02-07 : 14:44:36

Just put the rows into a temp table that has a sequential row number as the first column. Then order by that column.

Tara

Harry C
Posting Yak Master

148 Posts

Posted - 2005-02-07 : 14:53:27

quote:
Originally posted by tduggan

Just put the rows into a temp table that has a sequential row number as the first column. Then order by that column.

Tara

I saw examples like that, but this system would have thousands of users..and at least hundreds concurrently...would that even be efficient? What happens if two people create them at the same time?

quote:
Originally posted by chadmat

Would you still order them by the PK? There has to be something to order them by for paging to make sense.

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

Yes, I do still Order them, sorry I skipped that part, the whole SQL is
KnowledgeBase.KnowledgeBaseID,
ContentType.ContentTypeDisplayName,
KnowledgeBase.Keywords
FROM CourseAddendum INNER JOIN
KnowledgeBase ON CourseAddendum.KnowledgeBaseID = KnowledgeBase.KnowledgeBaseID INNER JOIN
ContentType ON KnowledgeBase.ContentTypeID = ContentType.ContentTypeID
WHERE (CourseAddendum.CourseID = @CourseID)
AND
KnowledgeBase.KnowledgeBaseID > @FirstRec
AND
KnowledgeBase.KnowledgeBaseID < @LastRec
ORDER BY KnowledgeBase.URL

Thanks for looking at this..

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-02-07 : 14:57:14

Temp tables are valid only to that session. So if you always use #Temp1 as the name, then you can have multiple copies of it for each user. One user will not be able to get to the other user's temp table. Now global variables are global to all users. But that would be ##Temp1 instead. So just one pound sign for local temporary tables.

Tara

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-02-07 : 15:07:49

to return rows e.g. from 37 to 58 (and pk with gaps):

select * from t where
(select count(*) from t tt where tt.pk<=t.pk)>=37
and
(select count(*) from t tt where tt.pk<=t.pk)<=58

Harry C
Posting Yak Master

148 Posts

Posted - 2005-02-07 : 17:09:25

quote:
Originally posted by tduggan

Temp tables are valid only to that session. So if you always use #Temp1 as the name, then you can have multiple copies of it for each user. One user will not be able to get to the other user's temp table. Now global variables are global to all users. But that would be ##Temp1 instead. So just one pound sign for local temporary tables.

Tara

works well enough...Is there any downside to using this method? As I said, there will be 100s of concurrent users? Any performance issues? Thanks alot for the help.

Harry C

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources