Diplaying top five employee names taking highest salary
Joined: Feb 09, 2004
I need a quary for which to displaying top 5 salary names from Employee Table [ February 09, 2004: Message edited by: G.MOHAN REDDY ]
Joined: Jul 21, 2003
hi, you can sort the table by salary in decending order and can take the first 5 records from that table. jyothsna
Joined: Aug 13, 2001
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.
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.
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
Joined: Jan 02, 2001
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;
Joined: Apr 17, 2003
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 ]
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.
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: