Specific row number after ordering

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

SQL Server Development (2000)

Specific row number after ordering

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-09-15 : 07:22:57

Anders writes "Please help an almost complete SQL newbie

I need only one thing. When I do this:

select service.per_id, service.res_priority, res_activation_time, loaner.name
from service,loaner
where service.marc_id=116752 AND res_type is not null and loaner.per_id=service.per_id
order by 2,3

I get a perfect list sorted in the correct manner, of customers queueing for books in a library system. Something like:

Per_id / Res_priority Res_activation_time etc.
745 / 0
389 / 5
102 / 5
999 / 9

I would like to extract on which row any of the Per_id's appear, based on this per_id, after being sorted and all. This to be able to tell any customer "You are nr X out of Y customers waiting for this book." Where X equals the number of the row for a specific per_id, and Y is the total of all rows. (I can accomplish the last manouver with a count(*). )

Perhaps the answer is already in your db, but in this case I just didn't get it."

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-09-15 : 08:28:40

Something like this:

select s.per_id, s.res_priority, s.res_activation_time, l.name,

(select count(*) from service ss where
ss.marc_id=s.mark_id and
ss.res_type is not null and
ss.res_priority<=s.res_priority and
ss.res_activation_time<=s.res_activation_time) as No

from service s inner join loaner l on s.per_id=l.per_id
where s.marc_id=116752 AND s.res_type is not null
order by 2,3

dsdeming

479 Posts

Posted - 2003-09-15 : 08:34:17

Try something like this:

select identity(int, 1, 1) as id, service.per_id, service.res_priority, res_activation_time, loaner.name
into #temp
from service,loaner
where service.marc_id=116752 AND res_type is not null and loaner.per_id=service.per_id
order by 2,3

select @iCount = @@ROWCOUNT
select @iPosition = id from #temp where per_id = @PersonToQueryFor

print 'You are number ' + cast( @iPosition as varchar( 12 )) + ' of ' +
cast( @iCount as varchar( 12 )) + ' customers waiting for this book.'

Dennis

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources