Have you ever faced the problem to get the nth highest record from the table. Here is the query to solve the problem. Say table called customer is defined in following way
CREATE TABLE Employee(
FirstName VARCHAR(10),
Salary INT,
)
INSERT INTO Employee VALUES ('Ted',2300)
INSERT INTO Employee VALUES ('John',4000)
INSERT INTO Employee VALUES ('George',600)
INSERT INTO Employee VALUES ('Mary',1100)
INSERT INTO Employee VALUES ('Sam',1700)
INSERT INTO Employee VALUES ('Doris',600)
INSERT INTO Employee VALUES ('Frank',3800)
INSERT INTO Employee VALUES ('Larry',500)
INSERT INTO Employee VALUES ('Sue',2900)
INSERT INTO Employee VALUES ('Sherry',1100)
INSERT INTO Employee VALUES ('Marty',2300)
use this query
SELECT RANK() OVER (ORDER BY Salary) AS [Rank by Salary],
FirstName,
Salary
FROM Employee
output
Rank by Salary FirstName Salary
-------------------- ---------- -----------
1 Larry 500
2 Doris 600
2 George 600
4 Mary 1100
4 Sherry 1100
6 Sam 1700
7 Ted 2300
7 Marty 2300
9 Sue 2900
10 Frank 3800
11 John 4000
|
| Author: Kunal 15 Oct 2009 | Member Level: Gold Points : 0 |
I have checked this and getting the same results. Try to run all the insert queries I have put and than check the results
|
| Author: Abhay 15 Oct 2009 | Member Level: Diamond Points : 1 |
Hi kunal,
I checked that once again.I think something was missing while copying so that i returned one result.Now it is approved.
|
| Author: Kunal 15 Oct 2009 | Member Level: Gold Points : 0 |
thanks
|
| Author: seema 16 Oct 2009 | Member Level: Gold Points : 1 |
SELECT * FROM (select COL1,COL2, dense_rank() OVER (order BY COLUMN_TO_SELECT_NTH_VAL) rank from TABLE_NAME) WHERE rank=N;
|