| SQL query [message #19271] |
Thu, 07 March 2002 20:26  |
vivek puranik
Messages: 1
Registered: March 2002
|
Junior Member |
|
|
How can I select the first highest,second highest and so on values in a numeric column using some common function?
Can anybody suggest such function?
For eg :
I have a table viz. Employee(Emp_Name,Emp_id,EMp_Sal)
I want to know the first highest,second highest and so on values of EMp_Sal column which is of type Number(10,2)
|
|
|
|
| Re: SQL query [message #19275 is a reply to message #19271] |
Thu, 07 March 2002 22:35  |
jeya
Messages: 18
Registered: January 2001
|
Junior Member |
|
|
select emp_sal from employee e where nn=(select count(*) from employee where emp_sal>=e.emp_sal)
'nn' refers to any integer (first highest or second highest..)
|
|
|
|
| Re: sorrrrrryyyy.......there is a mistake in my previous reply of SQL query [message #19277 is a reply to message #19271] |
Thu, 07 March 2002 23:25 |
jeya
Messages: 18
Registered: January 2001
|
Junior Member |
|
|
my previous reply will work only for distinct rows.if there are rows having same sal it won't work.
just try this..this will help u.
select emp_sal from employee e where nn=(select
count(*) from emplyee where sal>e.sal)
here nn is 0 from 1st higher,1 for 2nd higher,2 for 3rd higher...etc..
if 2 employees has the same sal say there r 2 persons getting first highest sal then there wont be second highest sal and so on.
|
|
|
|
| Re: SQL query [message #19296 is a reply to message #19271] |
Sat, 09 March 2002 16:27 |
Sudhakar Atmakuru
Messages: 58
Registered: May 2001
|
Member |
|
|
Try with DENSE_RANK OVER method.
Like:
SELECT ENO,ENAM,DENSE_RANK OVER (PARTITION BY DEPARTMENTCODE ORDER BY EMP_SALARY) FROM EMP_TABLE;
Good luck.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
