Click here to Skip to main content
12,067,916 members (29,516 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: SQL Oracle database
I want a SQL/PLSQL query to find the third highest value for a field (EX:Salary) from my table.
Posted 7-Jan-13 2:06am
Edited 7-Jan-13 2:15am
v2
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

  Permalink  
v2
Comments
Maciej Los 8-Jan-13 13:36pm
   
Good links, my 5!
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 5

You need to use one of the ranking functions[^].

For example, you can use ROW_NUMBER(). Try this:
DECLARE @myTable TABLE (aValue INT)
 
INSERT INTO @myTable (aValue)
VALUES(123)
INSERT INTO @myTable (aValue)
VALUES(456)
INSERT INTO @myTable (aValue)
VALUES(789)
INSERT INTO @myTable (aValue)
VALUES(234)
INSERT INTO @myTable (aValue)
VALUES(567)
INSERT INTO @myTable (aValue)
VALUES(345)
INSERT INTO @myTable (aValue)
VALUES(678)
INSERT INTO @myTable (aValue)
VALUES(999)
 
--view all records
SELECT ROW_NUMBER() OVER(ORDER BY aValue DESC) AS aPosition, aValue
FROM @myTable
Results:
1	999
2	789
3	678
4	567
5	456
6	345
7	234
8	123
--view only the third highest value
SELECT aValue As [ThirdHighestValue]
FROM (
    SELECT ROW_NUMBER() OVER(ORDER BY aValue DESC) AS aPosition, aValue
    FROM @myTable
) AS t1
WHERE t1.aPosition = 3

Result: 678
  Permalink  
v2
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

Try
SELECT a.Salary FROM MyTable a
WHERE 3 = (SELECT COUNT(DISTINCT (b.Salary)) FROM MyTable b WHERE a.Salary <= b.Salary)
  Permalink  
Comments
Maciej Los 8-Jan-13 13:36pm
   
Good work, my 5!
__TR__ 9-Jan-13 1:10am
   
Thank you.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 4

SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP 3 salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary
  Permalink  
v2
Comments
Maciej Los 8-Jan-13 13:36pm
   
Good answer, my 5!

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160208.1 | Last Updated 9 Jan 2013
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100