How to get the nth Highest mark in all branch?

Question

I have a table Student with fields: 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?

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

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

can you post sample data so that we can practically do someething rather then guessing — Muhammad Raheel, Dec 14 '12 at 07:39

Taryn · Accepted Answer · 2012-12-17T11:13:56.943

This would be much easier if MySQL had windowing functions like several of the other answers have shown. But they don't so you can use something like the following:

select student_id,
      student_name,
      branch,
      mark
from
(
  select student_id,
      student_name,
      branch,
      mark,
      @num := if(@branch = `branch`, @num + 1, 1) as group_row_number,
      @branch := `branch` as dummy,
      overall_row_num
  from
  (
    select student_id,
      student_name,
      branch,
      mark,
      @rn:=@rn+1 overall_row_num
    from student, (SELECT @rn:=0) r
    order by convert(replace(student_id, 'S', ''), signed) 
  ) src
  order by branch, mark desc
) grp
where group_row_number = 3
order by overall_row_num

See SQL Fiddle with Demo

The result would be:

| STUDENT_ID | STUDENT_NAME | BRANCH | MARK |
---------------------------------------------
|         S1 |         Amir |     EC |  121 |
|         S9 |         Wode |     CS |  123 |
|        S11 |          Den |     IT |  120 |

The expected Output is mentioned in the question. I need to get the third highest mark in all branch. In the result given here, it is given wrongly — Abi, Dec 17 '12 at 06:48
@Abi see my edit, I had to reverse the `order by`. It should be correct now. — Taryn, Dec 17 '12 at 10:47

score 0 · Answer 2 · edited Dec 14 '12 at 05:01

0

MAX()

select branch, MAX(mark) as 'highest mark' from Student group by branch;

edited Dec 14 '12 at 05:01

bobthyasian

933
5
17

answered Dec 14 '12 at 04:56

Miqdad Ali

6,129
7
31
50

[Similar Problem](http://stackoverflow.com/questions/1373559/mysql-join-based-on-maxtimestamp) – bobthyasian Dec 14 '12 at 05:01
this query will give the highest mark obtained in each branch and what i am asking that how it will give suppose 4th highest mark in each branch, and i assume OP is also asking the same ... – Pranav Dec 14 '12 at 05:05
http://stackoverflow.com/questions/32100/what-is-the-simplest-sql-query-to-find-the-second-largest-value – Miqdad Ali Dec 14 '12 at 05:13
Also refer this tooo http://www.sqlteam.com/article/find-nth-maximum-value-in-sql-server – Miqdad Ali Dec 14 '12 at 05:14

Pranav · Answer 3 · 2012-12-14T07:30:59.080

MySQl does not support CTE and also rownumber, so you have do something like this :- Make a temp table , insert data into that then select and finally drop temp table ..

create table #temp(branch varchar(30),mark int,row_numbers int)
Insert into #temp
SELECT a.branch, a.mark, count(*) as row_numbers FROM student a
JOIN student b ON a.branch = b.branch AND a.mark <= b.mark
GROUP BY  a.mark,a.branch
Order by  a.mark desc

select branch,mark from #temp where row_numbers=2

drop table #temp

This is for SQL SERVER :-

;WITH CTE AS
(
SELECT  Branch,mark, ROW_NUMBER() OVER (PARTITION BY Branch ORDER BY mark DESC) AS RowNum
FROM 
student
)
SELECT Branch,mark from cte
WHERE RowNum = 2

This will give suppose 2nd highest mark branch wise, you can accordingly choose any Nth level .

Hope it helps :-

This could take a long time, if my student database contains more than 1L data — Abi, Dec 14 '12 at 08:29

GKV · Answer 4 · 2012-12-14T13:55:39.713

0

This one works in oracle

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=nth_value;

nth_value:=nth highest mark needed. please check this sqlfiddle: http://sqlfiddle.com/#!4/7b559/3

edited Dec 14 '12 at 13:55

answered Dec 14 '12 at 05:55

GKV

501
4
8

score -1 · Answer 5 · edited Dec 14 '12 at 04:59

-1

You can use Limit to get n highest market ( limit 1,1 will give you 2nd highest, set as per your requirement)

SELECT mark, name
FROM student ORDER BY mark 
DESC LIMIT 1,1

edited Dec 14 '12 at 04:59

Pranav

8,563
4
26
42

answered Dec 14 '12 at 04:54

Suresh Kamrushi

15,627
13
75
90

This will not give branch wise resluts – Abi Dec 14 '12 at 05:24

How to get the nth Highest mark in all branch?

5 Answers5