Sql query to obtain 2nd highest mark in each subject for a student

Given a table with 3 columns StudentName,Subject and Marks.Write a sql to retrieve second highest Marks Obtained for each student.

Showing Answers 1 - 14 of 14 Answers

Tathagat Singh

  • Oct 1st, 2015
 

Code
  1. SELECT max(marks)FROM Student GROUP BY sub_name WHERE

  2. marks NOT IN (SELECT max(marks) FROM Student GROUP BY sub_name);

  Was this answer useful?  Yes

Jagan

  • Oct 25th, 2015
 

Code
  1. SELECT max(marks)FROM Student  WHERE marks NOT IN

  2. (SELECT max(marks) FROM Student GROUP BY sub_name)

  3. GROUP BY sub_name;

  Was this answer useful?  Yes

Jegatha Thiyagarajan

  • Nov 18th, 2015
 

Code
  1. Select * from(Select *,Row_number()Over(Partition by Subject Order by Marks Desc) As Rn from StudentTbl) where Rn=2

  Was this answer useful?  Yes

LizT

  • Dec 2nd, 2015
 

Code
  1. SELECT * FROM Students S

  2. WHERE 2 = (SELECT  COUNT(DISTINCT Marks) FROM Students S1

  3.                    WHERE S.Marks <= S1.Marks  AND S1.StudentName = S.StudentName)

  Was this answer useful?  Yes

KINGSTON

  • Apr 21st, 2016
 


SELECT MARK FROM STUDENT_MARK WHERE MARK NOT IN (SELECT MARK FROM STUDENT_MARK)

asha rose

  • May 10th, 2016
 

Code
  1. SELECT subject, marks FROM (SELECT subject, marks , Rank () OVER (PARTITION BY subject ORDER BY marks DESC ) mark_rank FROM STUDENT_MARK  ) WHERE  mark_rank = 2

  Was this answer useful?  Yes

Charlie

  • May 23rd, 2016
 

Here is the code with explanations.
SELECT * FROM table_name - display the entire row of data for the student
ORDER BY Marks DESC - sort by Marks by descending order
OFFSET 1 ROWS - skip the first value
FETCH NEXT 1 ROWS ONLY - return only 1 value (the 2nd highest in this case)

Code
  1. SELECT * FROM table_name

  2. ORDER BY Marks DESC

  3. OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY;

  Was this answer useful?  Yes

Ade

  • Jun 27th, 2016
 

Code
  1. SELECT TOP 2 (Marks)

  2. FROM (SELECT TOP (Marks) FROM TableName

  3. ORDER BY DESC) AS second highest Marks

  4. ORDER BY Marks

  Was this answer useful?  Yes

Ishan

  • Aug 23rd, 2016
 

The query given below will simply gives the second highest number but not in each subject.

  Was this answer useful?  Yes

Ishan Saxena

  • Aug 23rd, 2016
 

The question is to find 2nd highest in each subject. So, the right query for this will be:
WITH CteMarks AS (
SELECT SName, Subject, Marks
DENSE_RANK() OVER(PARTITION BY Subject ORDER BY Marks DESC) AS RowNum
FROM StudentDetails
)
SELECT SName, Subject, Marks
FROM CteMarks
WHERE RowNum = 2;
You can find nth position by just changing the RowNum = n

  Was this answer useful?  Yes

Rakesh

  • Aug 23rd, 2016
 

Try to avoid row_number while solving this kind of data because you will get wrong data when the starting record are duplicate. Try to use dense_rank

  Was this answer useful?  Yes

SANTHOSH KUMAR GUJJA

  • Nov 25th, 2017
 

SEL STUDENTNAME,MARKS FROM TABLE
QUALIFY ROW_NUMBER() OVER(PARTITION BY STUDENTNAME ORDER BY MARKS DESC)=2

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions