Finding the TOP values based on some Condetion | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

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

Select Collection,Code,Rank from PayTable T
where code in
(
select top 2 code from @PayTable
where Rank=T.Rank order by Collection DESC
)
order by Rank,Collection DESC
Madhivanan Failing to plan is Planning to fail
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
Only way is to use Dyanic SQL
Declare @TopVar int
Set @TopVar=3
EXEC(‘
Select Collection,Code,Rank from @PayTable T
where code in
(
select top ‘[email protected]+’ code from @PayTable
where Rank=T.Rank order by Collection DESC
)
order by Rank,Collection DESC’)
Madhivanan Failing to plan is Planning to fail
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 ‘[email protected]+’ 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
May I ask what version of SQL Server you’re using?
If it’s SQL Server 2005, you might want to check out ROW_NUMBER(). —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)
Thanks a Lot Experts For the Help
The query sent by nigelrivett is exactly what i wanted.
My Database is SQL Server 2000
Joseph
Nigel’s code is better in performancewise than the code I posted Madhivanan Failing to plan is Planning to fail
]]>