Home » Other » Training & Certification » Need to pick the 3rd largest(maximum) val from a table
Need to pick the 3rd largest(maximum) val from a table [message #278395] Sat, 03 November 2007 13:55 Go to next message
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
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 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Please have a look at OraFAQ Forum Guide before your next post.

You can check this.

By
Vamsi
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just search for "top n", there are many exemples here.

Regards
Michel
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 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
I'm first.... Razz

By
Vamsi
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 Go to previous messageGo to next message
Littlefoot
Messages: 20888
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.

./fa/2877/0/
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 Go to previous messageGo to next message
Frank
Messages: 7880
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
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 Go to previous messageGo to next message
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.
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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
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 Go to previous messageGo to next message
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))) ?
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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.
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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
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 Go to previous messageGo to next message
Seshagiri
Messages: 13
Registered: October 2007
Location: United Kingdom
Junior Member

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

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 Go to previous messageGo to next message
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.



Laughing Oh the DBA and users will be much happier knowing that.
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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

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 Go to previous messageGo to next message
Seshagiri
Messages: 13
Registered: October 2007
Location: United Kingdom
Junior Member

Oh yeah!
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 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
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

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 Go to previous messageGo to next message
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
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 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is the query you have now to get the third salary?

Regards
Michel
Previous Topic: sample database for studing
Next Topic: tables and users
Goto Forum:
  


Current Time: Sat Dec 03 04:13:06 CST 2016

Total time taken to generate the page: 0.70760 seconds