473,320 Members | 1,804 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Query to find the third largest row in a table

Hi everybody,

create table employee
{
empno char(5) primay key,
name varchar2(30),
salary number(5,2)
};

1. For the above table how to find the employee with the third highest salary.
Nov 13 '06 #1
22 13972
willakawill
1,646 1GB
Hi everybody,

create table employee
{
empno char(5) primay key,
name varchar2(30),
salary number(5,2)
};

1. For the above table how to find the employee with the third highest salary.
Hi. I would use a sub query and invert it;

Expand|Select|Wrap|Line Numbers
  1. SELECT TOP 1 empno, name, salary
  2. FROM employee
  3. WHERE empno IN
  4. (SELECT TOP 3 empno
  5. FROM employee
  6. ORDER BY salary DESC)
  7. ORDER BY salary
  8.  
Nov 14 '06 #2
ck1004
3
Hi everybody,

create table employee
{
empno char(5) primay key,
name varchar2(30),
salary number(5,2)
};

1. For the above table how to find the employee with the third highest salary.
SELECT DISTINCT (A.salary) FROM EMPLOYEE A WHERE &N= (SELECT COUNT (DISTINCT (B.salary) FROM EMPLOYEE B WHERE A.salary<=B.salary);
Enter value for n :3
Nov 15 '06 #3
willakawill
1,646 1GB
SELECT DISTINCT (A.salary) FROM EMPLOYEE A WHERE &N= (SELECT COUNT (DISTINCT (B.salary) FROM EMPLOYEE B WHERE A.salary<=B.salary);
Enter value for n :3
where is the employee?
Nov 15 '06 #4
suvam
31
u can try with this one ------
Select * from employee where emp_no = (
Select emp_no from (Select * from employee order by sal desc) where rownum < n+1
Minus
Select emp_no from (Select * from employee order by sal desc) where rownum < n
) ;
Enter n = 3 ;
Dec 6 '06 #5
pragatiswain
96 Expert
u can try with this one ------
Select * from employee where emp_no = (
Select emp_no from (Select * from employee order by sal desc) where rownum < n+1
Minus
Select emp_no from (Select * from employee order by sal desc) where rownum < n
) ;
Enter n = 3 ;
Howz this:

Select * from (Select * from employee order by sal desc) where rownum < n+1
Minus
Select * from (Select * from employee order by sal desc) where rownum < n
Dec 6 '06 #6
Select * from employee where empid = (
Select empid from (Select * from employee order by sal desc) where rownum < &n+1
Minus
Select empid from (Select * from employee order by sal desc) where rownum < &n
) ;
enter n=3
enter n=3
or

Select * from employee where empid = (
Select empid from (Select * from employee order by sal desc) where rownum < &n
Minus
Select empid from (Select * from employee order by sal desc) where rownum < &n-1
) ;
enter n=4
enter n=4

but here we have to provide n value twice is there any other method???
Dec 7 '06 #7
willakawill
1,646 1GB
Wow. so much effort to answer one question when the first answer works perfectly. Still there is room for all :)
Dec 8 '06 #8
This is the problem with the code u have given

SQL> SELECT TOP 1 empno, name, salary
2 FROM employee
3 WHERE empno IN
4 (SELECT TOP 3 empno
5 FROM employee
6 ORDER BY salary DESC)ORDER BY salary;
SELECT TOP 1 empno, name, salary
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
Dec 20 '06 #9
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;
Dec 20 '06 #10
Hey Man,

Try This...

SELECT * FROM
(
SELECT ROWNUM, EMPNO, NAME, SALARY FROM employee
ORDER BY SALARY DESC
)
WHERE ROWNUM < 4
MINUS
SELECT * FROM
(
SELECT ROWNUM, EMPNO, NAME, SALARY FROM employee
ORDER BY SALARY DESC
)
WHERE ROWNUM < 3
Dec 20 '06 #11
Hi everybody,

create table employee
{
empno char(5) primay key,
name varchar2(30),
salary number(5,2)
};

1. For the above table how to find the employee with the third highest salary.
hi ranjit
exequte this query u will get perfect ans
select sal from emp e where &n-1 = (select count(distinct(sal)) from emp d where e.sal < d.sal ) order by sal asc
Dec 27 '06 #12
select highest_sal from jobgrads where highest_sal=(select max(highest_sal) from jobgrads
where highest_sal <(select max(highest_sal) from jobgrads where highest_sal <
(select max(highest_sal) from jobgrads)))
Sep 26 '07 #13
select max(highest_sal) from jobgrads where highest_sal < (select max(highest_sal) from jobgrads where highest_sal <(select max(highest_sal) from jobgrads))
Sep 26 '07 #14
Hi everybody,

create table employee
{
empno char(5) primay key,
name varchar2(30),
salary number(5,2)
};

1. For the above table how to find the employee with the third highest salary.

here is the query to select third highest salary.

select level,max(sal) from employee where level=3 connect by prior sal > sal group by level;
Sep 27 '07 #15
as per oracle :

select empno, name, salary from (select * from employee order by salary desc) where rownum < :n+1 ;

Enter value for n :3.
Jan 18 '08 #16
select distinct (e.sal) from emp e
where 3>= (select count(*) from emp
where sal > e.sal ) order by sal desc

or

select * from (select * from emp order by sal desc)
where rownum =< n


-- n = 1,2,3,4,5,.............................
Jan 18 '08 #17
Hi everybody,

create table employee
{
empno char(5) primay key,
name varchar2(30),
salary number(5,2)
};

1. For the above table how to find the employee with the third highest salary.
Another Solution:
select a.name
from employee a,employee b
where a.salary>=b.salary
having count(*)=3
group by a.
name
Feb 27 '08 #18
Another Solution:
select a.name
from employee a,employee b
where a.salary>=b.salary
having count(*)=3
group by a.name
.

Small correction in the above Query...
The above Query will give the third smalles row...
Use the following Query to get the third highly salaried employee...

select a.name
from sale a,sale b
where a.sales<=b.sales
having count(*)=3
group by a.name
Feb 27 '08 #19
Hi everybody,

create table employee
{
empno char(5) primay key,
name varchar2(30),
salary number(5,2)
};

1. For the above table how to find the employee with the third highest salary.
Hi
Try this one
select level,max(sal)
from employee
where level=3
connect by prior sal>sal
groupby sal;
Feb 27 '08 #20
select distinct (e.sal) from emp e
where 3>= (select count(*) from emp
where sal > e.sal ) order by sal desc

or

select * from (select * from emp order by sal desc)
where rownum =< n


-- n = 1,2,3,4,5,.............................
HI GAURAV THIS IS SHIVA
UR SLUTION GIVES THE TOP N NO OF RECORDS
IS WHAT I FEEL
Feb 27 '08 #21
select max(sal) from (select distinct sal from emp order by sal desc)
where rownum =&n;

enter n =3;
Mar 2 '08 #22
Hi everybody,

create table employee
{
empno char(5) primay key,
name varchar2(30),
salary number(5,2)
};

1. For the above table how to find the employee with the third highest salary.

select max( salary ) from employee where salary < (select max( salary ) from employee where salary < (select max(salary) from employee where salary < ( select max( salary ) from employee )));
Mar 24 '08 #23

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: Brian Newsham | last post by:
------=_NextPart_000_004F_01C352B1.E5B8FA20 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, I'm working on a PHP based website that loads...
5
by: Bernie | last post by:
Greetings, I have 3 servers all running SQL Server 2000 - 8.00.818. Lets call them parent, child1, and child 2. On parent, I create a view called item as follows: CREATE view Item as...
1
by: Alex Satrapa | last post by:
I have a table from which I'm trying to extract certain information. For historical reasons, we archive every action on a particular thing ('thing' is identified, funnily enough, by 'id'). So the...
3
by: Tom Mitchell | last post by:
All: I'm stumped on a query. How do I find duplicates in a table where one of the duplicates has values is a certain field and the other doesn't. For example, I have the following table: ...
8
by: Maxi | last post by:
There is a lotto system which picks 21 numbers every day out of 80 numbers. I have a table (name:Lotto) with 22 fields (name:Date,P1,P2....P21) Here is the structure and sample data: ...
10
by: L. R. Du Broff | last post by:
I own a small business. Need to track a few hundred pieces of rental equipment that can be in any of a few dozen locations. I'm an old-time C language programmer (UNIX environment). If the only...
2
by: brob | last post by:
I am Access newbie looking for some guidance in putting together a query that can output a table of max values in a given column grouped by a couple of other columns. Here is an example of what I...
3
by: Idlemind23 | last post by:
Ahoy! I'm hoping for some help on what (I believe) should be a simple task. I have a form with a button. I would like that button to have a caption with a date. That date should be pulled out...
10
by: teddysnips | last post by:
My clients have asked me to maintain a database that was developed in- house. It's pretty good, considering the developer isn't a "programmer". The first thing they want me to do is to split it...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.