select TOP N - Different users

Question

select TOP N - Different users

skumar.klm

SSC Journeyman

Points: 85
More actions
March 2, 2010 at 5:48 am

#71898

I want to select top 10 records from a table,this time another user select top 10 records.
my question is how to avoid first top 10 in second selection.
this 2 process do at same time.Please comment.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323546 More actions · Answer 1

skumar.klm (3/2/2010)
I want to select top 10 records from a table,this time another user select top 10 records.
my question is how to avoid first top 10 in second selection.
this 2 process do at same time.Please comment.

skumar a TOP function should always have an ORDER by, so that the results are consistent:

ie SELECT TOP 10 * FROM TABLE ORDER BY DOWNLOADS DESC gives the top 10 most "downloaded", right?

if the data is user specific, it would need a WHERE clause:

SELECT TOP 10 * FROM TABLE WHERE SUPERVISOR = 'skumar' ORDER BY DOWNLOADS DESC that way, the top 10 for 'Lowell' is different than the top 10 for 'skumar', but still, my top 10 is consistent for me.

now if you were trying to say you want ten items in random order, that's a different issue, but still the same thing...you must have an order by:

SELECT TOP 10 * FROM TABLE ORDER BY newid() DESC

the newid() function is the trick needed to randomize some results...but if your table is huge, there will be a performance hit,and this can get slow.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

renu.iitkgp Mr or Mrs. 500 Points: 573 More actions · Answer 2

Are there actually two users who do this?

If so, the first user when he selects top 10 records, he can create a view let's say "UserView" with these 10 records.

the second user may write

select top 10 from Table_Name where UniqueId not in (select UniqueId from UserView) order by UniqueId desc

Let me know if actually one user wants to select the top 10 records, once and the next 10 records next and so on.

Your message needs a little more clarity.

Regards,

Venkat R. Prasad.