wood burning stoves*
The moose likes JDBC and the fly likes Diplaying top five employee names taking highest salary Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Diplaying top five employee names taking highest salary" Watch "Diplaying top five employee names taking highest salary" New topic
Author

Diplaying top five employee names taking highest salary

G.MOHAN REDDY
Greenhorn

Joined: Feb 09, 2004
Posts: 1
I need a quary for which to displaying top 5 salary names from Employee Table
[ February 09, 2004: Message edited by: G.MOHAN REDDY ]
jyothsna kumari
Ranch Hand

Joined: Jul 21, 2003
Posts: 108
hi,
you can sort the table by salary in decending order and can take the first 5 records from that table.
jyothsna
Ivan Tamayo
Ranch Hand

Joined: Aug 13, 2001
Posts: 49
That�s not a JSP Question. Is a SQL question.
With ANSI SQL you cannot make such a query, the best you can hope is select * from employees order by salary and take the first 5 records.
With propietary extensions is possible.
Ben Dover
Ranch Hand

Joined: Jan 30, 2004
Posts: 91
I think "quary" in this case is appropriate.
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 60810
    
  65

Moving to the JDBC forum.


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Gregg Bolinger
GenRocket Founder
Ranch Hand

Joined: Jul 11, 2001
Posts: 15299
    
    6

I took the liberty of removing a couple of posts that bordered on the "Be Nice" rule. The latter of the 2 was a quote of the first.
Not everyone knows where to post what when they first visit Javaranch. That is why we have the ability to move threads. Comments regarding such in any unhelpful way should be kept to yourself. We want everyone on Javaranch to have the best experience possible.
Ivan's got your answer. Go with that.


GenRocket - Experts at Building Test Data
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 60810
    
  65

If you aren't concerned with a specific database, some allow you to limit the number of returned results. PostgreSQL for example, sports the LIMIT keyword
(which, with the OFFSET keyword, makes paging through a large resultset trivial).
bear
Paul Misoni
Greenhorn

Joined: Jan 02, 2001
Posts: 16
If you'd like a more straightforward answer, try..
SELECT NAME, NUMBER
FROM TABLE1 A
WHERE 10 > (SELECT COUNT(*)
FROM TABLE1 B
WHERE A.NUMBER < B.NUMBER)
AND NUMBER IS NOT NULL
ORDER BY NUMBER DESC;
leo donahue
Ranch Hand

Joined: Apr 17, 2003
Posts: 327
Originally posted by G.MOHAN REDDY:
I need a quary for which to displaying top 5 salary names from Employee Table
[ February 09, 2004: Message edited by: G.MOHAN REDDY ]

In MS Access there is a way to do it, also using T-SQL. Try this webpage.


Thanks, leo
steve souza
Ranch Hand

Joined: Jun 26, 2002
Posts: 860
Depending on the database you are using there may be a simple proprietary solution. For example in Sybase you can do the following.


http://www.jamonapi.com/ - a fast, free open source performance tuning api.
JavaRanch Performance FAQ
steve souza
Ranch Hand

Joined: Jun 26, 2002
Posts: 860
Sybase allows
supun karunanayake
Greenhorn

Joined: May 11, 2004
Posts: 1
jdbc to excel data base connection pls help us sending the information
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30138
    
150

Supun,
Welcome to Javaranch. This question has been discussed a bunch of times in the past. Try searching this forum for excel. You will also want to look into ODBC.

Also, I think you hit the reply button instead of the new post button. Just something to be aware of for the future.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Loren Rosen
Ranch Hand

Joined: Feb 12, 2003
Posts: 156
Also, you need to think about how to deal with ties. Some databases have proprietary extensions that will handles ties (often they're documented as "data warehouse" features.
Amod Bhatia
Greenhorn

Joined: Jun 04, 2006
Posts: 10
Originally posted by Paul Misoni:
If you'd like a more straightforward answer, try..

SELECT NAME, NUMBER
FROM TABLE1 A
WHERE 10 > (SELECT COUNT(*)
FROM TABLE1 B
WHERE A.NUMBER < B.NUMBER)
AND NUMBER IS NOT NULL
ORDER BY NUMBER DESC;



I still not able to get the above query ...can any body pls expalain it with example
it will be gr8 full to me

Thanks
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 60810
    
  65

Originally posted by Amod Bhatia:

can any body pls expalain it with example
it will be gr8 full to me


Amod Bhatia, JavaRanch is a community of people from all over the world, many of who are not native English speakers. While using abbreviations like "gr8" instead of spelling out "great" is convenient when text messaging your friends on a cell phone or in a chat room, it presents an extra challenge to those that are already struggling with English. Additionally, such shortcuts may confound automated translation tools that patrons of the Ranch may be making use of.

I would like to ask for your help in making the content of JavaRanch a little easier to read for everybody that visits here by not using such abbreviations.

Please read this for more information.

thanks,
bear
JavaRanch Sheriff
[ June 04, 2006: Message edited by: Bear Bibeault ]
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333


I still not able to get the above query


It's a really really slow, naive way of solving the problem; for a table with N rows, that query will take on the order of N**2 operations to produce an answer. In other words, it will work fine for a table with maybe 100 rows, and you will be waiting a long time on a table with 100,000 rows. It also is a query for top 10, when the original question was for top 5.

The problem is that, in plain language, that SQL says:
for every number in the table, count the numbers in the entire table that are greater than the current number, and if there are less than 10 numbers in the entire table that are greater than the current number, include the current number in the result.

It would be far better to simply select the rows in the correct order, and use Statement.setMaxRows() to limit the transfered result.

As earlier posts mention, there are also database-specific ways to have a SQL query limit itself. On the Oracle databse, it would be:

Other database has other ways....
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Diplaying top five employee names taking highest salary
 
Similar Threads
Inheritence - Any suggestions?
Scanner class question
How to retrieve the last 10 records in a table.
Help with Constructor!
Invalid column index