need help on select top N
-
I have the following tables and data,
declare @tApplyProgram table (myID varchar(50), programID varchar(10), stSVT char(1) null)
insert into @tApplyProgram(myID,programID) values('1925','184');
insert into @tApplyProgram(myID,programID) values('4474','172');
insert into @tApplyProgram(myID,programID) values('8890','172');
insert into @tApplyProgram(myID,programID) values('5578','172');
insert into @tApplyProgram(myID,programID) values('2980','184');
insert into @tApplyProgram(myID,programID) values('2500','172');
insert into @tApplyProgram(myID,programID) values('1925','180');
insert into @tApplyProgram(myID,programID) values('5578','180');
/*myID and programID is unique*/
declare @tRulesH table (programID varchar(50), noOfMinSubject tinyint)
insert into @tRulesH values('172',2)
insert into @tRulesH values('184',1)
insert into @tRulesH values('160',3)
/*programID is unique*/
declare @tResult table (myID varchar(50), subjectCd varchar(50), merit tinyint)
insert into @tResult values('1925','01', 90)
insert into @tResult values('1925','02', 56)
insert into @tResult values('1925','03', 78)
insert into @tResult values('4474','03', 78)
insert into @tResult values('4474','04', 45)
insert into @tResult values('4474','05', 77)
insert into @tResult values('5578','01', 90)
insert into @tResult values('5578','02', 56)
insert into @tResult values('5578','03', 78)
insert into @tResult values('2980','01', 90)
insert into @tResult values('2980','02', 56)
/*myID and subjectCd is unique*/
1. I need to select top N * from @tResult order by merit desc based on each @tApplyProgram(myID)
2. select top N, which N is taken from @tRulesH(noOfMinSubject)
I've no idea 'how select top N, which N is taken from @tRulesH(noOfMinSubject)?
Results are expected as follow
myID | programID | subjectCd | merit
------------------------------------------
1925 184 01 90
4474 172 03 78
4474 172 05 77
8890 172 NULL NULL
8890 172 NULL NULL
5578 172 01 90
5578 172 03 78
2980 184 01 90
/*
myID | programID
----------------------
1925 180
5578 180
was not displayed because it's @tApplyProgram(programID) has no rows in @tRulesH
*/
I'm stuck
-
I think there's a discrepancy in your results, one of the 8890 should be 2500?
with cte as (select p.myID, p.programID, p.stSVT, r.subjectCd, r.merit, h.noOfMinSubject,
row_number() over(partition by p.myID, p.programID order by r.merit desc) as rn
from @tApplyProgram p
left outer join @tResult r on r.myID=p.myID
inner join @tRulesH h on h.programID=p.programID)
select myID,programID,subjectCd,merit
from cte
where rn <= noOfMinSubject;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Per Books Online (Top clause):
Specifies that only the first set of rows will be returned from the query result. The set of rows can be either a number or a percent of the rows. The TOP expression can be used in SELECT, INSERT, UPDATE, and DELETE statements.
Since N is going to be a value from a table, you might have to turn your final SELECT into dynamic SQL to get the result that you want.
EDIT: I only say this about dynamic SQL because the statements "Select top N" don't indicate that your teacher wants you to filter using a WHERE clause on this specific assignment.
-
Mark-101232 (4/13/2012)
I think there's a discrepancy in your results, one of the 8890 should be 2500?with cte as (select p.myID, p.programID, p.stSVT, r.subjectCd, r.merit, h.noOfMinSubject,
row_number() over(partition by p.myID, p.programID order by r.merit desc) as rn
from @tApplyProgram p
left outer join @tResult r on r.myID=p.myID
inner join @tRulesH h on h.programID=p.programID)
select myID,programID,subjectCd,merit
from cte
where rn <= noOfMinSubject;
Kindest Mark-101232,
Your guidance is an inspiration for me
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply