如何获得每个分支的第n个最高分？

I have a table Student with fields: Student_id, Student_Name, Mark, Branch.

我有一个表有学生的表格:Student_id,Student_Name,Mark,Branch。

I want to get the nth highest mark and name of each branch with in a single query. Is it possible?

我想在一个查询中获得每个分支的第n个最高标记和名称。可能吗?

for Example if the datas are

例如,如果数据是

S1   |  Amir  |  EC  |  121
S2   |  Ewe   |  EC  |  123
S3   |  Haye  |  EC  |  45
S4   |  Mark  |  EC  |  145
S5   |  Tom   |  CS  |  152
S6   |  Hudd  |  CS  |  218
S7   |  Ken   |  CS  |  48
S8   |  Ben   |  CS  |  15
S9   |  Wode  |  CS  |  123
S10  |  Kayle |  IT  |  125
S11  |  Den   |  IT  |  120
S12  |  Noy   |  IT  |  126
S13  |  Jon   |  GS  |  150

And I am selecting to display the third highest mark in each branch the output should be like

我选择在输出应该是的每个分支中显示第三个最高标记

S1   | Amir   | EC   | 121
S9   | Wode   | CS   | 123
S11  | Den    | IT   | 120
S13  |  Jon   |  GS  |  150

I tried the following solution:

我尝试了以下解决方案:

select Student_id,Student_Name,Mark,Branch from (
    select  Student_id,Student_Name,Mark,Branch,dense_rank() over (partition by Branch order by Mark desc) rnk
    from Student ) where rnk=3;

But the result is:

但结果是:

 S1   | Amir   | EC   | 121
 S9   | Wode   | CS   | 123
 S11  | Den    | IT   | 120

Because there is only one entry for Branch 'GS'. How can I get my desired result

因为分支'GS'只有一个条目。我怎样才能得到我想要的结果

2 个解决方案

#1

select Student_id,Student_Name,Mark,Branch from (
    select  Student_id,Student_Name,Mark,Branch,dense_rank() over (partition by Branch order by Mark desc) rnk
    from Student ) x where rnk=3
    union

    select Student_id,Student_Name,Mark,Branch from student
where Branch in  ( 
     select  Branch
    from Student 
    group by Branch
    having count(*) =1
    )

#2

use CTE and UNION

使用CTE和UNION

--DECLARE TABLE
declare @temp table
(Student_id nvarchar(55), Student_Name nvarchar(55), Branch nvarchar(55), Mark int)

insert into @temp values ('S1 ','Amir','EC',121)
insert into @temp values ('S2 ','Ewe ','EC',123)
insert into @temp values ('S3 ','Haye','EC',45 )
insert into @temp values ('S4 ','Mark','EC',145)
insert into @temp values ('S5 ','Tom ','CS',152)
insert into @temp values ('S6 ','Hudd','CS',218)
insert into @temp values ('S7 ','Ken ','CS',48 )
insert into @temp values ('S8 ','Ben ','CS',15 )
insert into @temp values ('S9 ','Wode','CS',123)
insert into @temp values ('S10','Kayl','IT',125)
insert into @temp values ('S11','Den ','IT',120)
insert into @temp values ('S12','Noy ','IT',126)
insert into @temp values ('S13','Jon ','GS',150)
insert into @temp values ('S14','JonS','GS',100)

-- MAKE CTE
;WITH CTE AS (
select *,RN = ROW_NUMBER() OVER (PARTITION BY Branch ORDER BY Mark DESC) from @temp
)
--QUERY
SELECT * FROM CTE WHERE RN=3
UNION
SELECT TOP 1 * FROM CTE 
WHERE Branch NOT IN 
(SELECT Branch FROM CTE WHERE RN=3)