Home » SQL & PL/SQL » SQL & PL/SQL » selecting max five sal from a table
selecting max five sal from a table [message #343566] Wed, 27 August 2008 09:30 Go to next message
Ramakrishna.Tata
Messages: 1
Registered: August 2008
Location: hyderabad
Junior Member
Hi forum,
Can any body give me solution for "HOW TO GET THE MAX FIVE SALARIES FROM A TABLE"

Regards
Ramakrishna.
Re: selecting max five sal from a table [message #343572 is a reply to message #343566] Wed, 27 August 2008 09:50 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
I am sorry to see that both GOOGLE & the Search function on this forum are broken for you.

Repairs are being made.
Please wait patiently.
Additional response will occur after repairs have been completed.

http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above
Re: selecting max five sal from a table [message #343757 is a reply to message #343566] Thu, 28 August 2008 01:19 Go to previous messageGo to next message
hamidmahmood
Messages: 30
Registered: August 2006
Member
This is your query

SELECT ename,sal
FROM (SELECT ename,sal FROM emp ORDER BY sal DESC)
WHERE
ROWNUM<6
Re: selecting max five sal from a table [message #343779 is a reply to message #343757] Thu, 28 August 2008 02:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@hamidmahmood,

Follow the guidelines:
- format your post
- don't give solution just clue

OraFAQ Forum Guide, "How to format your post?" section.
OraFAQ Forum Guide, "Responding to Posts" section:
Quote:
When responding to questions, if it is obviously a student with a homework assignment or someone else just learning, especially in the homework and newbies forums, it is usual to provide hints or clues, perhaps links to relevant portions of the documentation, or a similar example, to point them in the right direction so that they will research and experiment on their own and learn, and not provide complete solutions to problems. In cases where someone has a real-life complex work problem, or at least it may seem complex to them, it may be best to provide a complete demo and explanation.


Regards
Michel

Re: selecting max five sal from a table [message #343799 is a reply to message #343566] Thu, 28 August 2008 03:39 Go to previous messageGo to next message
swarit_gupta
Messages: 4
Registered: August 2008
Junior Member
Hi,

best and simple way will be -

arrange the result from select command in desc order by using ORDER BY Clause.
then you can use cursor and fetch 5 rows with the help of while or FOR loop.


DECLARE
CURSOR dummy_cur
IS
SELECT int_field
FROM dummy_table order by int_field desc;

----int_field is the name of the column

dummy_row dummy_cur%ROWTYPE;
i number := 1;
BEGIN
OPEN dummy_cur;
WHILE i < 6
LOOP

FETCH dummy_cur INTO dummy_row;
DBMS_OUTPUT.put_line (dummy_row.int_field);
i := i + 1;
END LOOP;

CLOSE dummy_cur;
END;
--

Tried and tested
It might help you
Thanks


Re: selecting max five sal from a table [message #343815 is a reply to message #343799] Thu, 28 August 2008 04:26 Go to previous messageGo to next message
dwarak.k
Messages: 61
Registered: June 2008
Location: Hyderabad
Member
Swarit ,Why do you want to write a PL/SQL block when it could be achieved by simple SQL? Razz
Re: selecting max five sal from a table [message #343837 is a reply to message #343566] Thu, 28 August 2008 05:54 Go to previous messageGo to next message
aparangi_rhl
Messages: 10
Registered: January 2006
Junior Member
Hi Ramakrishna,

You can also retrieve the MAX 5 salaries by using co-related queries. Try to write a query creating 2 aliases of the same table and use correlation. The hint is you should use count() function in the correlated sub query.
Re: selecting max five sal from a table [message #343849 is a reply to message #343566] Thu, 28 August 2008 06:05 Go to previous messageGo to next message
SUHAS22845
Messages: 51
Registered: August 2008
Location: BANGALORE
Member

use order by and rownum concept.
Re: selecting max five sal from a table [message #343851 is a reply to message #343566] Thu, 28 August 2008 06:08 Go to previous messageGo to next message
SUHAS22845
Messages: 51
Registered: August 2008
Location: BANGALORE
Member


By using the sub query, we can write.. here u go buddy . .

in the inner query first order by salary desc and include rownumber. in the outerside use rownumber rn < 6
Re: selecting max five sal from a table [message #344470 is a reply to message #343851] Fri, 29 August 2008 22:46 Go to previous messageGo to next message
swarit_gupta
Messages: 4
Registered: August 2008
Junior Member
Agree we can use by ROWNUM also.....

I have given PL/SQL as it can be done by it as well...
we know how to do with SAL and PL/SQL

Normal SQL method was there in the forum....thats why given that PL/SQL method

Regards Shocked
Re: selecting max five sal from a table [message #344496 is a reply to message #344470] Sat, 30 August 2008 01:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Next time you want to post code, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.

Regards
Michel
Re: selecting max five sal from a table [message #344518 is a reply to message #343566] Sat, 30 August 2008 03:14 Go to previous messageGo to next message
ashok_manian
Messages: 3
Registered: August 2008
Junior Member
select salary,rownum from(select distinct salary,ename from employees order by salary desc)where rownum<=5

[Updated on: Sat, 30 August 2008 03:15]

Report message to a moderator

Re: selecting max five sal from a table [message #344519 is a reply to message #344518] Sat, 30 August 2008 03:17 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Once again read OraFAQ Forum Guide.
1/ "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.
2/ "Responding to Posts" section:
Quote:
When responding to questions, if it is obviously a student with a homework assignment or someone else just learning, especially in the homework and newbies forums, it is usual to provide hints or clues, perhaps links to relevant portions of the documentation, or a similar example, to point them in the right direction so that they will research and experiment on their own and learn, and not provide complete solutions to problems. In cases where someone has a real-life complex work problem, or at least it may seem complex to them, it may be best to provide a complete demo and explanation.


In addition, your "solution" is wrong. The same and correct one has been given 2 days ago (with the same comments from me).

Regards
Michel
Previous Topic: data migration from db2 to oracle (merged)
Next Topic: Log All Mails Send by Database Server Using UTL_SMTP
Goto Forum:
  


Current Time: Sat Dec 10 10:35:14 CST 2016

Total time taken to generate the page: 0.10935 seconds