Tathagat Singh

• Oct 1st, 2015

 

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


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

Jagan

• Oct 25th, 2015

 

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


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


GROUP BY sub_name;

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
SELECT MAX(MARKS) FROM STUDENT WHERE MARKS < (SELECT MAX(MARKS) FROM STUDENT);

LizT

• Dec 2nd, 2015

 

Code
SELECT * FROM Students S 


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


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

KINGSTON

• Apr 21st, 2016

 

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

asha rose

• May 10th, 2016

 

Code
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

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
SELECT * FROM table_name


ORDER BY Marks DESC


OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY;

Ade

• Jun 27th, 2016

 

Code
SELECT TOP 2 (Marks)


FROM (SELECT TOP (Marks) FROM TableName


ORDER BY DESC) AS second highest Marks


ORDER BY Marks

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