| shikhu |
Posted: Jul 03, 2007 10:24:47 AM |
Total Post: 61
Joined: Jul, 2006
|
| how can we find the 3rd max salary in a single statement. |
|
|
 Ratnaker |
| Posted: Jul 04, 2007 02:54:54 AM |  |
|
Total Post: 136
Joined: Apr, 2007
|
Sikku
SELECT salary
FROM (SELECT salary FROM employees ORDER BY salary DESC)
WHERE ROWNUM <= 3;
K P ratnaker
|
|
|
|
|
| chinna |
| Posted: Jul 04, 2007 05:06:43 AM | |
|
Total Post: 27
Joined: May, 2007
|
Hi shieku,
Query:
SELECT MIN(SAL) FROM (SELECT SAL FROM EMP ORDER BY SAL DESC) WHERE ROWNUM<=3;
take care
byee
thanks
srawan
|
|
|
|
|
| shikhu |
| Posted: Jul 04, 2007 10:22:38 AM | |
|
Total Post: 61
Joined: Jul, 2006
|
no, its wrong it'll not give the required output .
the right query is:
select b.sal from
(select distinct(sal) from emp) a,
(select distinct(sal) from emp)b
where a.sal>=b.sal
group by (b.sal)
having count(b.sal)=3
|
|
|
|
|
| chinna |
| Posted: Jul 04, 2007 11:41:28 PM | |
|
Total Post: 27
Joined: May, 2007
|
hi,
could you justfiy your answer.
wt's wrong in my query.
and it is genralized one we can replace 3 with any value.
byee
|
|
|
|
|
| swinal |
| Posted: Jul 07, 2007 09:31:32 AM | |
|
Total Post: 4
Joined: Jul, 2007
|
hi shikhu.
I think u want to execute only 3rd max salary not another records, right?
Ur question is related to the "TOP N ANALYSIS" concept.
The right answer would be
SELECT rownum VROWNUM ,salary
FROM (SELECT rownum,salary FROM employees ORDER BY desc)
WHERE VROWNUM = 3;
if u want to execute the complete record of the person who gets 3rd max salary then use all the col names in select list.
i hope the query will help u.
|
|
|
|
|
| swinal |
| Posted: Jul 08, 2007 07:46:00 AM | |
|
Total Post: 4
Joined: Jul, 2007
|
if u want to retrive only 3rd max salary this query will give u the proper o/p
SELECT salary
FROM (SELECT rownum v_rownum,salary FROM employees ORDER BY desc)
WHERE VROWNUM = 3;
if want to retrive the complete record then
SELECT *
FROM (SELECT rownum v_rownum,first_name, salary **{all colum names }***
FROM employees ORDER BY desc)
WHERE VROWNUM = 3;
i hope this will help u
|
|
|
|
|
| daisy |
| Posted: Jul 19, 2007 06:49:10 AM | |
|
Total Post: 1
Joined: Jul, 2007
|
Order by clause is working under from clause query
you can try this one
select b.basic_rt,b.empcode from
(select distinct(basic_rt),empcode from emp_rate_master) a,
(select distinct(basic_rt),empcode from emp_rate_master) b
where a.basic_rt>=b.basic_rt
group by (b.basic_rt),b.empcode
having count(b.basic_rt)=3;
|
|
|
|
|
| positive |
| Posted: Jun 29, 2008 08:57:49 PM | |
|
Total Post: 361
Joined: Jun, 2008
|
visit:
http://neworacledba.blogspot.com/
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
