How to find 2nd highest value in a table ?

March 21st, 2010 Jaffar

Tweet

To Find the 2nd Maximum Of Mark in a Data Set

SELECT * FROM Student a WHERE 2=(SELECT COUNT(DISTINCT Mark)
FROM Student b WHERE a.Mark<=b.Mark)

To Find the 2nd Minimum Of Mark in a Data Set

SELECT * FROM Student a WHERE 2=(SELECT COUNT(DISTINCT Mark)
FROM Student b WHERE a.Mark>=b.Mark)

To Find nth Maximum and Minimum

SELECT * FROM Student a WHERE n=(SELECT COUNT(DISTINCT Mark)
FROM Student b WHERE a.Mark<=b.Mark)

SELECT * FROM Student a WHERE n=(SELECT COUNT(DISTINCT Mark)
FROM Student b WHERE a.Mark>=b.Mark)

replace n with the corresponding number

Popularity: 2% [?]

•  
•  
•  
•  
•  
•  
•  
•  
•  
•  
•  
•  
•  
•  
•  
•  
•  

Get Shareaholic

Related posts:

1. Using OVER() with Aggregate Functions in Sql Server 2005
2. Create Database,Table and Procedure in Sql Server – Beginners
3. Create backup copy of table in SQLServer – (Select Into)

Posted in Sql Server, Tips And Tricks

« How to Text Wrap a Label in WPF?

How to override the DNS locally ? »

You can leave a response, or trackback from your own site.

2 Responses to “How to find 2nd highest value in a table ?”

1. afyanor says:

   May 5, 2010 at 2:57 am

   I am using SQL Server 2008. I have a Student table in which there are following fields:
   1. StudentId, 2. StudentName, 3. Marks .

   I want to get a resultset in which there should be a column named “Position”. Something like “Select StudentId,StudentName,Marks, as Position from Student” so that, depending on the marks a student scored, i can evaluate them as the 1st, 2nd or 20th position. If students have the same marks, then they have the same position.

   Thanks in advance.
   afyanor
2. afyanor says:

   May 5, 2010 at 4:02 pm

   Thanks for your articles Jaffar.
   I got what i was looking for at:
   http://msdn.microsoft.com/en-us/library/ms189798.aspx