OraFAQ Forum: Training & Certification » Need to pick the 3rd largest(maximum) val from a table

Home » Other » Training & Certification » Need to pick the 3rd largest(maximum) val from a table

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Need to pick the 3rd largest(maximum) val from a table [message #278395]

Sat, 03 November 2007 13:55

subash15aug
Messages: 2
Registered: November 2007
Location: Chennai, India

Junior Member

hI,
In oracle 9i... I would like to pick the maximum 3rd val from the table.

Ex: From the emp table i need to select the employee who has the 3rd maximum sal...

Could you please help me

Report message to a moderator

Re: Need to pick the 3rd largest(maximum) val from a table [message #278396 is a reply to message #278395]

Sat, 03 November 2007 13:59

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

Senior Member

Please have a look at OraFAQ Forum Guide before your next post.

You can check this.

By
Vamsi

Report message to a moderator

Re: Need to pick the 3rd largest(maximum) val from a table [message #278397 is a reply to message #278395]

Sat, 03 November 2007 13:59

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Just search for "top n", there are many exemples here.

Regards
Michel

Report message to a moderator

Re: Need to pick the 3rd largest(maximum) val from a table [message #278398 is a reply to message #278397]

Sat, 03 November 2007 14:01

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

Senior Member

I'm first.... Razz

By
Vamsi

Report message to a moderator

Re: Need to pick the 3rd largest(maximum) val from a table [message #278399 is a reply to message #278398]

Sat, 03 November 2007 14:41

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Nice of you, guys, but you didn't actually answer the question.

So, may I try? It is Jones who has the 3rd largest salary.

/forum/fa/2877/0/

Report message to a moderator

Re: Need to pick the 3rd largest(maximum) val from a table [message #278403 is a reply to message #278399]

Sat, 03 November 2007 15:16

Frank
Messages: 7901
Registered: March 2000

Senior Member

So, in order for the original poster to be able to send in his homework assigment:

select ename
,      sal as third_highest
from   emp
where  ename = 'JONES'
and    instr('top_salaries', 'top') < 3

Report message to a moderator

Re: Need to pick the 3rd largest(maximum) val from a table [message #278703 is a reply to message #278395]

Mon, 05 November 2007 07:37

Seshagiri
Messages: 13
Registered: October 2007
Location: United Kingdom

Junior Member

Read Analytic Functions using
rank(), dense_rank() or row_number().

A crude method of getting the answer is

(SELECT MAX(sal)
FROM emp
WHERE sal <
(SELECT MAX(sal)
FROM emp
WHERE sal <
(SELECT MAX(sal)
FROM emp)));

See the pattern 3rd highest salary and so 3 selects.

Report message to a moderator

Re: Need to pick the 3rd largest(maximum) val from a table [message #278709 is a reply to message #278703]

Mon, 05 November 2007 07:50

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

This does not answer the question:

Quote:

From the emp table i need to select the employee who has the 3rd maximum sal...

Regards
Michel

Report message to a moderator

Re: Need to pick the 3rd largest(maximum) val from a table [message #278710 is a reply to message #278395]

Mon, 05 November 2007 07:56

Seshagiri
Messages: 13
Registered: October 2007
Location: United Kingdom

Junior Member

You mean

SELECT * FROM EMP WHERE SAL = (SELECT MAX(sal)
FROM emp
WHERE sal <
(SELECT MAX(sal)
FROM emp
WHERE sal <
(SELECT MAX(sal)
FROM emp))) ?

Report message to a moderator

Re: Need to pick the 3rd largest(maximum) val from a table [message #278712 is a reply to message #278710]

Mon, 05 November 2007 07:59

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

It is "better" or at least it answers the question. Cool

Regards
Michel

[Updated on: Mon, 05 November 2007 08:00]

Report message to a moderator

Re: Need to pick the 3rd largest(maximum) val from a table [message #278715 is a reply to message #278712]

Mon, 05 November 2007 08:10

Seshagiri
Messages: 13
Registered: October 2007
Location: United Kingdom

Junior Member

Thank you. It is just that simple.
Now if you want the 113th highest salary,
observe the pattern, construct an SQL dynamically using 113 as the parameter. Run it and return the answer.

Report message to a moderator

Re: Need to pick the 3rd largest(maximum) val from a table [message #278721 is a reply to message #278715]

Mon, 05 November 2007 08:31

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

Run it and return the answer

In about a year as you have to run 114 full table scans. Laughing

Regards
Michel

Report message to a moderator

Re: Need to pick the 3rd largest(maximum) val from a table [message #278731 is a reply to message #278395]

Mon, 05 November 2007 08:53

Seshagiri
Messages: 13
Registered: October 2007
Location: United Kingdom

Junior Member

It doesn't require that many full scans. It only requires 113.

Report message to a moderator

Re: Need to pick the 3rd largest(maximum) val from a table [message #278733 is a reply to message #278731]

Mon, 05 November 2007 08:55

MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA

Senior Member

Seshagiri wrote on Mon, 05 November 2007 08:53

It doesn't require that many full scans. It only requires 113.

Oh the DBA and users will be much happier knowing that.

Report message to a moderator

Re: Need to pick the 3rd largest(maximum) val from a table [message #278734 is a reply to message #278731]

Mon, 05 November 2007 08:58

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Explain plan for third salary:

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Plan hash value: 1147809240

--------------------------------------------------------------------------------
| Id  | Operation               | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |     1 |    87 |    12   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL      | EMP  |     1 |    87 |     3   (0)| 00:00:01 |
|   2 |   SORT AGGREGATE        |      |     1 |    13 |            |          |
|*  3 |    TABLE ACCESS FULL    | EMP  |     1 |    13 |     3   (0)| 00:00:01 |
|   4 |     SORT AGGREGATE      |      |     1 |    13 |            |          |
|*  5 |      TABLE ACCESS FULL  | EMP  |     1 |    13 |     3   (0)| 00:00:01 |
|   6 |       SORT AGGREGATE    |      |     1 |    13 |            |          |
|   7 |        TABLE ACCESS FULL| EMP  |    14 |   182 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("SAL"= (SELECT MAX("SAL") FROM "EMP" "EMP" WHERE "SAL"<
              (SELECT MAX("SAL") FROM "EMP" "EMP" WHERE "SAL"< (SELECT MAX("SAL")
              FROM "EMP" "EMP"))))
   3 - filter("SAL"< (SELECT MAX("SAL") FROM "EMP" "EMP" WHERE "SAL"<
              (SELECT MAX("SAL") FROM "EMP" "EMP")))
   5 - filter("SAL"< (SELECT MAX("SAL") FROM "EMP" "EMP"))

Note
-----
   - dynamic sampling used for this statement

You need the extra one to get the employee data. Wink

Regards
Michel

Report message to a moderator

Re: Need to pick the 3rd largest(maximum) val from a table [message #278736 is a reply to message #278395]

Mon, 05 November 2007 09:10

Seshagiri
Messages: 13
Registered: October 2007
Location: United Kingdom

Junior Member

Oh yeah!

Report message to a moderator

Re: Need to pick the 3rd largest(maximum) val from a table [message #278774 is a reply to message #278736]

Mon, 05 November 2007 11:43

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

Senior Member

Congrats! You have learned a lot.

Didn't you get a chance to look at the earlier posts?
If not, have a look at them now.

By
Vamsi

Report message to a moderator

Re: Need to pick the 3rd largest(maximum) val from a table [message #279456 is a reply to message #278774]

Thu, 08 November 2007 10:09

subash15aug
Messages: 2
Registered: November 2007
Location: Chennai, India

Junior Member

Thats, really great! Thanks for ur replies. My doubt is cleared and i have learnt a lot

Regards,
subash

Report message to a moderator

Re: Need to pick the 3rd largest(maximum) val from a table [message #279557 is a reply to message #279456]

Fri, 09 November 2007 00:44

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

What is the query you have now to get the third salary?

Regards
Michel

Report message to a moderator

Previous Topic:	sample database for studing
Next Topic:	tables and users

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Nov 24 05:21:11 CST 2024