Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

how do I select the top n records 1

Status
Not open for further replies.
tomandlis

tomandlis

IS-IT--Management
May 28, 2003
12
US
How can I select the top 'n' records w/o a cursor where 'n' is a value in a table? I've constructed two sample tables and a udf (below) that shows what I'm trying (unsuccessfully) to do.

Essentially, I'm trying to allow my users to rank players (football) and then define how many of the top ranked players they would like to see. Here is my code (you should be able to paste it into your query analyzer and run it.

Thanks!


--**************************************
--snip
--**************************************
use comm2
set nocount on
--first create a table that will hold player
--scores per user, i.e., users
--will score the same player differently
drop table tblRating
create table dbo.tblRating
(
idUser int,
idPlayer int,
nmScore numeric
)
go
insert into tblRating(idUser, idPlayer, nmScore)
select 1, 1, 100

insert into tblRating(idUser, idPlayer, nmScore)
select 1, 2, 103

insert into tblRating(idUser, idPlayer, nmScore)
select 1, 3, 95

insert into tblRating(idUser, idPlayer, nmScore)
select 1, 4, 10

insert into tblRating(idUser, idPlayer, nmScore)
select 2, 1, 80

insert into tblRating(idUser, idPlayer, nmScore)
select 2, 2, 107

insert into tblRating(idUser, idPlayer, nmScore)
select 2, 3, 200

insert into tblRating(idUser, idPlayer, nmScore)
select 2, 4, 99

--now create a user table that shows how many
--players they want to rank
--in this example user '1' only want to see the
--top 2 players per his score system
--and user '2' wants to see the top 3 per his system
drop table tblUser
create table tblUser
(
idUser int,
selectTopN int
)

insert into tblUser(idUser, selectTopN)
select 1, 2

insert into tblUser(idUser, selectTopN)
select 2, 3

--now create a function that gives each player a
--ranking, e.g., 1, 2, 3 based upon
--the rank order of score per user
drop function udf_rankPlayers
go
create FUNCTION dbo.udf_rankPlayers
(
@idUser int
)
RETURNS @t TABLE
(
idUser int,
idRank int identity(1,1),
idPlayer int,
nmScore numeric
)
AS
BEGIN


INSERT @t (idUser, idPlayer, nmScore)
select idUser, idPlayer, nmScore
from tblRating
where idUser = @idUser
order by nmScore desc

RETURN
END



GO

--now use the function to retreive
--the rankings for any user.
--This works fine, but isn't quite what I want
declare @idUser int
select @idUser = 1

select *
from dbo.udf_rankPlayers(@idUser)
where idRank <= 2

--Below is what I want. Note that I get
--multiple rows for each row in tblUser,
--i.e., each user gets their own rankings (some
--may want to see the top 2, top 3 ... top n)
--based upon their player scores.

/*
select *
from tblUser u
cross join (
select *
from dbo.udf_rankPlayers(u.idUser)
) z
*/
--I am open to workarounds, but I DON'T want a
--cursor--too slow, in the real system
--there are ~50 thousand users
--and ~500 players and I don't
--want to open up a 50,000 X 500
--= 25,000,000 million record cursor
--I don't want to open a cursor at all
--if it is avoidable

--FYI, I have experimented with a query
--that coalesces the udf into one row
--of comma delimited values and returned
--it in the sql select. This got
--all the idPlayers per user into one
--comma delimited string, but then I had
--to use a 50,000 record cursor on
--tblUser to build and exec stmt with the
--comma delimited string--MESSY AND SLOW. I need better.

--**************************************
--snip
--**************************************

declare @idUser1 int
declare @list varchar(255), @sql varchar(512)

declare tUser cursor for
select idUser
from tblUser

OPEN tUser
FETCH NEXT FROM tUser INTO @idUser1
WHILE @@FETCH_STATUS = 0
BEGIN

select @list = coalesce(@list + ', ', '') + cast(idPlayer as varchar(2))
from dbo.udf_rankPlayers(@idUser1) r
join tblUser u
on u.idUser = r.idUser
where r.idRank <= selectTopN


select @sql = 'select idUser, idPlayer from tblRating where idPlayer in (' + @list + ') and idUser = ' + cast(@idUser1 as varchar(2)) + ' order by nmScore desc'

exec(@sql)

set @list = null

FETCH NEXT FROM tUser INTO @idUser1
END
CLOSE tUser
DEALLOCATE tUser

 
Sort by date Sort by votes
I didn't read your post any further than the first paragraph, so this may not be what you want.
Code: 
DEClARE @Top int
SET @Top=(Some query that returns a single int)
SET @@RowCount @Top
SELECT statement
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]
 
Upvote 0 Downvote
Thanks Donutman, but that code would require a cursor and that is what I am trying to avoid.
 
Upvote 0 Downvote
btw, donutman, how did you get your code to come in that nice neat block.
 
Upvote 0 Downvote
You use [/code] Your code here
Code: 
 but make the one with the slash the trailing one.
   BTW, that code doesn't require a cursor.  @@RowCount tells the engine to return only the first x number of rows (usually from a sorted recordset).  
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]
 
Upvote 0 Downvote
Yes, I'm familiar with @@recordcount, but if you paste my code into query analyzer I think you'll see that you can't apply @@recordcount w/o a cursor because the recordcount I want comes from a table: tbluser.selectTopN.
 
Upvote 0 Downvote
I think the reason you can't user @@recordcount is that you aren't just setting recordcount once, you need to set it for each user in tblUser

Here is the code again using the 'code' brackets

Code: 
--**************************************
--snip
--**************************************
use comm2
set nocount on 
--first create a table that will hold player 
--scores per user, i.e., users 
--will score the same player differently
drop table tblRating
create table dbo.tblRating
(
idUser    int,
idPlayer    int,
nmScore    numeric
)
go
insert into tblRating(idUser, idPlayer, nmScore)
select 1, 1, 100

insert into tblRating(idUser, idPlayer, nmScore)
select 1, 2, 103

insert into tblRating(idUser, idPlayer, nmScore)
select 1, 3, 95

insert into tblRating(idUser, idPlayer, nmScore)
select 1, 4, 10

insert into tblRating(idUser, idPlayer, nmScore)
select 2, 1, 80

insert into tblRating(idUser, idPlayer, nmScore)
select 2, 2, 107

insert into tblRating(idUser, idPlayer, nmScore)
select 2, 3, 200

insert into tblRating(idUser, idPlayer, nmScore)
select 2, 4, 99

--now create a user table that shows how many 
--players they want to rank
--in this example user '1' only want to see the 
--top 2 players per his score system
--and user '2' wants to see the top 3 per his system
drop table tblUser
create table tblUser
(
idUser    int,
selectTopN    int
)

insert into tblUser(idUser, selectTopN)
select 1, 2

insert into tblUser(idUser, selectTopN)
select 2, 3

--now create a function that gives each player a 
--ranking, e.g., 1, 2, 3 based upon 
--the rank order of score per user
drop function udf_rankPlayers
go
create FUNCTION dbo.udf_rankPlayers
(
    @idUser    int
)
RETURNS @t TABLE 
(
    idUser    int,
    idRank    int    identity(1,1),
    idPlayer    int,
    nmScore    numeric
) 
AS
BEGIN 


    INSERT @t (idUser, idPlayer, nmScore)
    select idUser, idPlayer, nmScore
    from tblRating
    where idUser = @idUser
    order by nmScore desc

    RETURN
END



GO

--now use the function to retreive 
--the rankings for any user.  
--This works fine, but isn't quite what I want
declare @idUser    int
select @idUser = 1

select *
from dbo.udf_rankPlayers(@idUser)
where idRank <= 2

--Below is what I want.  Note that I get 
--multiple rows for each row in tblUser, 
--i.e., each user gets their own rankings (some 
--may want to see the top 2, top 3 ... top n)
--based upon their player scores.

/*
select *
from tblUser u
cross join (
    select *
    from dbo.udf_rankPlayers(u.idUser)
) z
*/
--I am open to workarounds, but I DON'T want a 
--cursor--too slow, in the real system
--there are ~50 thousand users 
--and ~500 players and I don't 
--want to open up a 50,000 X 500 
--= 25,000,000 million record cursor
--I don't want to open a cursor at all 
--if it is avoidable

--FYI, I have experimented with a query 
--that coalesces the udf into one row
--of comma delimited values and returned 
--it in the sql select.  This got 
--all the idPlayers per user into one 
--comma delimited string, but then I had
--to use a 50,000 record cursor on 
--tblUser to build and exec stmt with the 
--comma delimited string--MESSY AND SLOW.  I need better.

--**************************************
--snip
--**************************************

declare @idUser1    int
declare @list varchar(255), @sql varchar(512)

declare tUser cursor for
select idUser
from tblUser

OPEN tUser
FETCH NEXT FROM tUser INTO @idUser1
WHILE @@FETCH_STATUS = 0
BEGIN

select @list = coalesce(@list + ', ', '') + cast(idPlayer as varchar(2))
from dbo.udf_rankPlayers(@idUser1) r
join tblUser u 
on u.idUser = r.idUser
where r.idRank <= selectTopN


select @sql = 'select idUser, idPlayer from tblRating where idPlayer in (' + @list + ') and idUser = ' + cast(@idUser1 as varchar(2)) + ' order by nmScore desc'

exec(@sql)

set @list = null

FETCH NEXT FROM tUser INTO @idUser1
END
CLOSE tUser
DEALLOCATE tUser
 
Upvote 0 Downvote
tomandlis said:
Yes, I'm familiar with @@recordcount, but if you paste my code into query analyzer I think you'll see that you can't apply @@recordcount w/o a cursor because the recordcount I want comes from a table: tbluser.selectTopN.
Click to expand...
Are you aware that @@RowCount will accept a variable? I haven't studied your code, but I don't want to unless I have to...I'm away from my server so I can't paste your code into QA.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]
 
Upvote 0 Downvote
A couple of questions:
Why do you want all the users ratings back at once. Surely any user will only wish to see there or a specific someone else's ratings.

[I would do it using a temp variable table.
try this and see if it does what you need and I dont know how efficient it is.

Code: 
declare @v_table TABLE (vidUser int, vidPlayer int, vnmScore numeric, vSelectTopN int, vMyRank int)
INSERT INTO @v_Table (vidUser, vidPlayer, vnmScore, vSelectTopN)
Select rat.*, us.SelectTopN from tblRating rat
inner join (SELECT idUser, selectTopN FROM tblUser) us on us.idUser = rat.idUser

order by rat.idUser, nmscore desc

Declare @vRank int, @v_LastUser int
SET @v_LastUser = 0
Update @v_Table
SET 
	vMyRank = 
		CASE WHEN @vRank IS NULL THEN 1
		    ELSE @vRank + 1
		END, 
	@vRank = 
		CASE WHEN @vRank IS NULL THEN 0
			WHEN @vRANK IS NOt NULL AND @v_LastUser <> vidUser THEN 0
			ELSE @vRank + 1
		END,
	@v_LastUser = vidUser 
	
select * from @v_table
where vMyRank <=vSelectTopN


"I'm living so far beyond my income that we may almost be said to be living apart
 
Upvote 0 Downvote
It seems to perform ok, but would need indexes similiar to this to prevent sort and bookmark lookups.
It wont be quick, but it should perform quicker than a Cursor though.

Code: 
--primarily used for joining tables.
 CREATE  CLUSTERED  INDEX [idx_idUser] ON [dbo].[tblUser]([idUser]) ON [PRIMARY]
GO

--use this as a foreign key, but also as a covering index
 CREATE  INDEX [idx_fk_isuser] ON [dbo].[tblRating]([idUser], [nmScore] DESC , [idPlayer]) ON [PRIMARY]
GO

"I'm living so far beyond my income that we may almost be said to be living apart
 
Upvote 0 Downvote
A couple of questions:
Why do you want all the users ratings back at once. Surely any user will only wish to see there or a specific someone else's ratings.
Click to expand...

This is a nightly run that caches everybody's result at once. It is also a step in a process and the next step needs to know the user rankings (depth chart) in order to determine other things like who should start in the next game.

hmckillop, It looks like your code will work. I'll let you know. Thanks!
 
Upvote 0 Downvote
Man, I've seen that trick setting variables in an update statement about a dozen times now and I always forget about it when I need it. Thanks again!
 
Upvote 0 Downvote
btw, is the table variable any quicker than just making a temp table?
 
Upvote 0 Downvote
A table variable is in memory, where available, but once it start to takes up more memory than available to it, it become a physical table, so it may be more efficient to have a actual temp table rather than a table variable for large rows. (Also would allow you to create indices on it.)


"I'm living so far beyond my income that we may almost be said to be living apart
 
Upvote 0 Downvote
so a table variable doesn't allow indexes? is that right?
 
Upvote 0 Downvote
Technically no, it allows a primary keys, unique key and nulls (constraints), but no indexes.
table variables are cleaned up automatically at the end of the function, stored procedure, or batch in which they are defined.

table variables used in stored procedures result in fewer recompilations of the stored procedures than when temporary tables are used.


Transactions involving table variables last only for the duration of an update on the table variable. Thus, table variables require less locking and logging resources
Click to expand...


"I'm living so far beyond my income that we may almost be said to be living apart
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

WomanPro
  • Locked
  • Question
Insert into query adds records to unreadable format, how do I change that? 1
Replies
5
Views
240
Chris Miller
Chris Miller
grnzbra
  • Locked
  • Question
SELECT TOP 1 of Each of a Group
Replies
5
Views
174
grnzbra
grnzbra
grnzbra
  • Locked
  • Question
How Do I Add Column to Middle of Table
Replies
4
Views
363
BobSuruncle
BobSuruncle
leifoet
  • Locked
  • Question
Error in Select query
Replies
14
Views
288
Andrzejek
Andrzejek
leifoet
  • Locked
  • Question
How can I update the input of a form (rows and columns) to the database in one query ?
Replies
1
Views
151
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top