Find Second Max value in the Table SQL

Question

4.00/5 (1 vote)

See more:

ID Name price
1 ram 1000
2 ram 3000
3 raj 2000
4 krish 1000
5 sai 8000
6 raj 6000
7 ram 2000

In that table , i want to find out the second maximum price for 'ram'.
How to find it.

Thanks in Advance

Posted 24-Nov-14 0:17am

Gokul Athithan

Add a Solution

4 solutions

Solution 4

Here is another one, using top:

SQL

select top 1 *
from (
    select top 2 *
    from theTable
    where lower(name) = 'ram'
    order by price desc
) MaxPriceTop2
order by price asc
;

And another one using the OFFSET FETCH clause, MSSqlServer 2012+:

SQL

select *
from theTable
where lower(name) = 'ram'
order by price desc
    offset 1 rows
    fetch next 1 rows only
;

Posted 24-Nov-14 5:53am

jaket-cp

Updated 25-Nov-14 23:55pm

v2

Solution 5

SQL

WITH myTableWithRows AS (
        SELECT (ROW_NUMBER() OVER ( PARTITION BY NAME ORDER BY myTable.price DESC)) as row,name ,*
        FROM myTable)
    SELECT * FROM myTableWithRows WHERE row = 2 and name='ram'

Posted 25-Nov-14 1:35am

Ashish_Pathania

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Manas Bhardwaj · Accepted Answer · 2014-11-24T00:26:00

Solution 1

Like this:

SQL

SELECT MAX(Price) FROM MyTable
WHERE Price NOT IN (SELECT MAX(Price) FROM MyTable WHERE Name = 'Ram')
AND  Name = 'Ram'

Posted 24-Nov-14 0:26am

Manas Bhardwaj

Updated 24-Nov-14 2:50am

v2

Comments

Maciej Los 24-Nov-14 6:30am

Manas, OP wants to get second max value!

Manas Bhardwaj 24-Nov-14 8:51am

Hi Maciej,

Yes, but this should be ok. The subquery removes the maximum and thus you get second max.

or did I miss something?

Maciej Los 24-Nov-14 9:22am

OK, i see it now.
+5

OriginalGriff · Accepted Answer · 2014-11-24T00:26:00

Solution 2

Try:

SQL

WITH myTableWithRows AS (
    SELECT (ROW_NUMBER() OVER (ORDER BY myTable.price DESC)) as row,*
    FROM myTable)
SELECT * FROM myTableWithRows WHERE row = 2

Posted 24-Nov-14 0:26am

OriginalGriff

Comments

Maciej Los 24-Nov-14 6:31am

+5!
[EDIT]
You forgot to add Partition by [Name] and where statement [Name]='Ram'. Please, improve your answer, Paul.

RossMW 24-Nov-14 20:33pm

Interesting... but what happens when there are two or more records with the max price. It comes down to the exact meaning of second maximum price. May need to distinct the prices.