Finding the TOP values based on some Condetion

I have a table 'PayTable' with fields Code, Collection and Rank

CREATE TABLE PayTable
(
Code varchar(10) NULL,
Collection decimal NULL,
Rank varchar(10) NULL
)

INSERT PayTable VALUES('101', 500, 'R1')
INSERT PayTable VALUES('102', 625, 'R2')
INSERT PayTable VALUES('103', 750, 'R1')
INSERT PayTable VALUES('104', 125, 'R1')
INSERT PayTable VALUES('105', 175, 'R2')
INSERT PayTable VALUES('106', 450, 'R2')
INSERT PayTable VALUES('107', 825, 'R2')
INSERT PayTable VALUES('108', 445, 'R1')
INSERT PayTable VALUES('109', 735, 'R2')
INSERT PayTable VALUES('110', 930, 'R1')
INSERT PayTable VALUES('111', 975, 'R2')


The Rank field can contain any values ie R1, R2, R3....
Code field is the Primary Key

I need a simple sql statement without using cursers that gives me the Top 2 Collections, Code and Rank in Each Ranks given in the Table
ie my output should be
930 110 R1
750 103 R1
975 111 R2
825 107 R2

my Query returns only the top 2 values avaliable in the list

select TOP 2(Collection), Code, Rank from PayTable
group by Collection, Code, Rank
order by Collection desc

Please Help

Thanks for the Reply.....

I want to give different values to the TOP key word ie when i write this in a stored procedure.
Sometimes i want to get top 3, 4, 5 etc
please help

Something like this will do it without dynamic sql.
I'm assuming that you can have duplicate collections within a rank but not collection, code.
Take out the second bit of the num calculation if collection is unique.

declare @i int
select @i = 3
select Rank, Collection, Code
from
(
select Rank, Collection, Code,
num = (select count(*) from PayTable t2 where t2.Rank = t1.Rank and (t2.Collection > t1.Collection or (t2.Collection = t1.Collection and t2.Code > t1.Code))) + 1
from PayTable t1
) ord
where num <= @i
order by Rank, Collection desc, code desc

i have inserted one more value to the table

INSERT PayTable VALUES('115', 500, 'R1')

and want the top 2 values from each rank

if i use the dsql by Madhivanan i will get the result of Rank R1 as
750103R1
500101R1

and that of nigelrivett i will get the result as
R1750103
R1500115

There is a difference of employ code 101 and 115 in the queries

If there is more than two entry for the top two positions (ie there is a repatition for the top collection) where employ 101 and 115 have both collection of 500 each and both are eligible for second spot, then how can i modify the query to include those values.

i need the result as
R1750103
R1 500 101
R1500115
R2825107
R2625102
R3948113
R3945114


Please Help

That's because the code I gave takes the top two in order of collection then code.
Madhivanan gives it in order of collection only - order of code is indeterminate and could change from run to run.

for all codes for top two collections

declare @i int
select @i = 3
select Rank, Collection, Code
from
(
select Rank, Collection, Code,
num = (select count(*) from PayTable t2 where t2.Rank = t1.Rank and (t2.Collection > t1.Collection) + 1
from PayTable t1
) ord
where num <= @i
order by Rank, Collection desc, code desc

for the dynamic sql

Declare @TopVar int
Set @TopVar=3

EXEC('Select Collection,Code,Rank
from @PayTable T
where collection in (select top '+@TopVar+' collection from @PayTable
where Rank=T.Rank order by collection DESC)
order by Rank,Collection DESC')

Thanks for the help Mr. nigelrivett

The Query does not fully solve my problem

The Table may contain thousands of entries and if i try to take the top 5 entries then if the top most spot is occupied by collection say 999 and 10 or more entries have this same collection value for a particular rank and all these entries are eligible for top position.
Even if i took top 5 i need all the eligible entries in the top 5 spot for each rank.
ie if there is more than 1 entries for the 5th position then the query should show those entries also.
So each rank can contain 5, 6... entried depending on the top 5 entries collection values


Please Help

That will give all the entries in the top 5 collections.
It sounds like you want the top 5 entries and ties ordered by collection.

wrong
it does give the top 5 entries and ties.

Not sure what you want:
That gave the top 5 entries and ties ordered by collection

If you look at the subquery that gives the order

select Rank, Collection, Code,
num = (select count(*) from PayTable t2 where t2.Rank = t1.Rank and (t2.Collection > t1.Collection or (t2.Collection = t1.Collection and t2.Code > t1.Code))) + 1
from PayTable t1
order by Rank, num

You will see that this gives the sequence for each row.

select Rank, Collection, Code,
num = (select count(*) from PayTable t2 where t2.Rank = t1.Rank and (t2.Collection > t1.Collection)) + 1
from PayTable t1
order by Rank, num

This gives the same value for each entry in the collection - the next collection starting from the number in the preceeding :

so
declare @i int
select @i = 3
select Rank, Collection, Code
from
(
select Rank, Collection, Code,
num = (select count(*) from PayTable t2 where t2.Rank = t1.Rank and (t2.Collection > t1.Collection)) + 1
from PayTable t1
) ord
where num <= @i
order by Rank, Collection desc, code desc

will give ties.

My actual scenario is I want to give a Prize to top 5 (may vary) employees who have brought the top most collection. So i can't avoid the 6th member who also have the same collection as that of the fifth member.

Not sure what the problem is.
That will include ties.
If you have 5 in the top collection and 4 in the next it will give the top 5 for 1 to 5 then include the next 4 if you select 6.

if there are 8 members who have exactly the same collection and also this collection is the top most collection of the rank, i need the top 5 members then if i work with the prev: query i have to avoid 3 members who are also eligible for the top 5 position. So even if i take top 5 i need to get all the 8 for that rank and for other ranks i need to get only 5 codes(where no such situations exists)

Please help me

>> if there are 8 members who have exactly the same collection and also this collection is the top most collection of the rank, i need the top 5 members then if i work with the prev: query i have to avoid 3 members who are also eligible for the top 5 position.

That's the first query I gave - it gives 5 entries per rank only.

You seem a bit confused about the requirement though. You said in the previous posts

>> if there is more than 1 entries for the 5th position then the query should show those entries also.
So each rank can contain 5, 6... entried depending on the top 5 entries collection values

>> My actual scenario is I want to give a Prize to top 5 (may vary) employees who have brought the top most collection. So i can't avoid the 6th member who also have the same collection as that of the fifth member.

So you were saying you wanted the top entries and all those in the same collections.
Now you are saying you just want the top entries.

To help you clarify try this situation.

CREATE TABLE PayTable
(
Code varchar(10) NULL,
Collection decimal NULL,
Rank varchar(10) NULL
)

INSERT PayTable VALUES('101', 503, 'R1')
INSERT PayTable VALUES('102', 502, 'R1')
INSERT PayTable VALUES('103', 502, 'R1')
INSERT PayTable VALUES('104', 502, 'R1')
INSERT PayTable VALUES('105', 501, 'R1')

You select 3 entries
Do you want codes 101, 104, 105 (that's the first query I gave).
or do you want 101, 102, 103, 104 (that's the last query I gave).

The two queries are
declare @i int
select @i = 3
select Rank, Collection, Code
from
(
select Rank, Collection, Code,
num = (select count(*) from PayTable t2 where t2.Rank = t1.Rank and (t2.Collection > t1.Collection or (t2.Collection = t1.Collection and t2.Code > t1.Code))) + 1
from PayTable t1
) ord
where num <= @i
order by Rank, Collection desc, code desc

declare @i int
select @i = 3
select Rank, Collection, Code
from
(
select Rank, Collection, Code,
num = (select count(*) from PayTable t2 where t2.Rank = t1.Rank and (t2.Collection > t1.Collection)) + 1
from PayTable t1
) ord
where num <= @i
order by Rank, Collection desc, code desc

Thanks a Lot Experts For the Help
The query sent by nigelrivett is exactly what i wanted.
My Database is SQL Server 2000


Joseph