How to get second Max value of mark list?

Question

I used MAX function.

How to get the second highest maths mark from the database.

e.g: (maths : 96 , 88 , 55);

SELECT MAX(maths) FROM mark;

how do I get 88 from SQL query?

Please provide sample data, desired results, and an explanation of the logic you want. — Gordon Linoff, Dec 24 '18 at 12:49
Possible duplicate of [Get the second highest value in a MySQL table](https://stackoverflow.com/questions/5360894/get-the-second-highest-value-in-a-mysql-table) — hardik solanki, Dec 24 '18 at 13:00
Possible duplicate of [What is the simplest SQL Query to find the second largest value?](https://stackoverflow.com/questions/32100/what-is-the-simplest-sql-query-to-find-the-second-largest-value) — Sayed Mohd Ali, Dec 24 '18 at 13:03

score 3 · Accepted Answer · answered Dec 24 '18 at 13:00

3

SELECT MAX( column ) FROM table WHERE column < ( SELECT MAX( column ) FROM table )

answered Dec 24 '18 at 13:00

SNS

score 2 · Answer 2 · answered Dec 24 '18 at 12:49

2

If you want the second highest mark, you would use limit/offset:

SELECT DISTINCT maths
FROM mark
ORDER BY maths DESC
LIMIT 1, 1;

answered Dec 24 '18 at 12:49

Gordon Linoff

e.g: (maths : 96 , 88 , 55); how to I get 88 in the SQL query – R.Surya Dec 24 '18 at 12:56

score 2 · Answer 3 · answered Dec 24 '18 at 12:59

2

You could use a subquery to get the overall maximum and then get the maximum of those values less the overall maximum.

SELECT max(maths)
       FROM mark
       WHERE math < (SELECT max(maths)
                            FROM mark);

answered Dec 24 '18 at 12:59

sticky bit

score 1 · Answer 4 · answered Dec 24 '18 at 12:54

1

select maths from mark order by maths desc limit 1 offset 1

answered Dec 24 '18 at 12:54

Ankur Goel

e.g: (maths : 96 , 88 , 55); how to I get 88 in the SQL query – R.Surya Dec 24 '18 at 12:56

score 1 · Answer 5 · answered Dec 24 '18 at 13:03

1

SELECT MAX( maths ) FROM mark WHERE maths < ( SELECT MAX( maths ) FROM mark )

answered Dec 24 '18 at 13:03

Sayed Mohd Ali

score 0 · Answer 6 · answered Dec 24 '18 at 12:57

0

Try this query

 SELECT MAX(maths) FROM mark WHERE maths NOT IN ( SELECT Max(maths) FROM mark);

answered Dec 24 '18 at 12:57

Mik_ko

score 0 · Answer 7 · answered Dec 24 '18 at 13:06

0

The below code will help you.

SELECT DISTINCT mark
FROM testing
ORDER BY mark DESC
LIMIT 1, 1

And I just attached my table screen for your reference.

answered Dec 24 '18 at 13:06

Ramya

7 Answers7