Tathagat Singh

• Oct 1st, 2015

 

Code
 

Jagan

• Oct 25th, 2015

 

Code
 

Jegatha Thiyagarajan

• Nov 18th, 2015

 

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

grd5143

 Profile Answers by grd5143 Questions by grd5143

• Nov 28th, 2015

 

Code
 

LizT

• Dec 2nd, 2015

 

Code
 

KINGSTON

• Apr 21st, 2016

 

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

asha rose

• May 10th, 2016

 

Code
 

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
 

Ade

• Jun 27th, 2016

 

Code
 

Rakesh057

 Profile Answers by Rakesh057 Questions by Rakesh057

• Aug 11th, 2016

 

My advice is that try to use dense_rank because when will have duplicate records that time, your answer will give wrong output.

Ishan

• Aug 23rd, 2016

 

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

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

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

SANTHOSH KUMAR GUJJA

• Nov 25th, 2017

 

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

Rachana J K

• Jan 10th, 2022

 

Select student_name, marks from Student order by marks desc limit(2,1) group by sub-name;

Zienab

• Mar 26th, 2022

 

Select Z.first_name, Z.last_name, Z.salary
From
(SELECT EMP.first_name, EMP.last_name, EMP.salary,
RANK() OVER( Order by EMP.salary DESC) as Ranking
FROM employees EMP
) Z
Where Z.Ranking = 2;

Code