0

Consider a table with two columns: mark and name

I need to get the second highest value, and the name of the second highest value.

Rajesh Thiru
  • 11
  • 3
  • 1
    What have you already tried? What are you having trouble with? SQL Server, MySQL, etc? – Tyler Roper Jan 30 '19 at 04:56
  • Possible duplicate of (**MySQL**) [Find max and second max salary for a employee table MySQL](https://stackoverflow.com/questions/21520038/find-max-and-second-max-salary-for-a-employee-table-mysql), (**SQL Server**) https://stackoverflow.com/questions/7417415/how-to-get-second-highest-salary-employees-in-a-table, (**Oracle**) https://stackoverflow.com/questions/16293298/how-can-i-select-the-record-with-the-2nd-highest-salary-in-database-oracle – Tyler Roper Jan 30 '19 at 04:58

3 Answers3

1

You can use ROW_NUMBER(), RANK(), DENSE_RANK() functions in SQL. But in this case, you'll have to use DENSE_RANK() because there may be a condition where 2 or more students may have scored maximum marks, in such case you can't use ROW_NUMBER() or RANK().

Learn more about this functions click here

SELECT * FROM (
    SELECT name, mark, DENSE_RANK() over (order by mark desc) RankNo 
    FROM tablename
    ) AS Result 
WHERE Result.RankNo = 2
Jiten Parmar
  • 26
  • 1
  • 7
  • 1
    I'd remove the brackets in `[RankNo]` in order to get a standard SQL query. You can make it `AS RankNo` to get it more readable. – Thorsten Kettner Jan 30 '19 at 06:27
  • 1
    actually I was using [Rank] but then I thought using keyword under sqaure brackets can misguide him. Anyways thanks for the suggestion. – Jiten Parmar Jan 30 '19 at 06:30
0
SELECT * 
FROM   (SELECT name, 
               mark, 
               Row_number() over(ORDER BY mark DESC) AS rownums 
        FROM   employees) 
WHERE  rownums = 2;
Grijesh Chauhan
  • 57,103
  • 20
  • 141
  • 208
Anand Krishnan
  • 1
  • 2
  • 2
    Please provide some context for your answer : https://stackoverflow.com/help/how-to-answer – tchap Feb 15 '19 at 10:57
-1
SELECT name,mark FROM table ORDER BY mark desc limit 1,1

This code sort all records by mark in descending order. limit 1,1 skips the first result (first 1 = first record) and then returns the next result (second 1 = second record).

Wiimm
  • 2,971
  • 1
  • 15
  • 25