This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JDBC and the fly likes how to get top 5 result from database Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "how to get top 5 result from database" Watch "how to get top 5 result from database" New topic
Author

how to get top 5 result from database

Alvin chew
Ranch Hand

Joined: Jan 08, 2004
Posts: 834
hi, good day, assume i have 500 records , and the records is actually the data of customer who have make transaction in a company, my question is how to i decide who are the top 5 customers who having deal with highest transaction volume in company.. anyone have idea ?

thank you very much for your time !
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

If you are using oracle then RANK and DENSE_RANK will give you desired result.


Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
java programer
Greenhorn

Joined: Nov 05, 2004
Posts: 3
If u r using MySQL

SELECT <column names> FROM <Table Name> ORDER BY <transation volume> DESC LIMIT 0,5;

Probably most databases will support this query
Adeel Ansari
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
for better and more accurate answer. you need to give the table structure mate.
Srinivasa Raghavan
Ranch Hand

Joined: Sep 28, 2004
Posts: 1228
Select * from (select * from transation order by tranAmount desc ) where rownum < 6;

May be some sort of query like this helps you.. Also it works only for oracle.


Thanks & regards, Srini
MCP, SCJP-1.4, NCFM (Financial Markets), Oracle 9i - SQL ( 1Z0-007 ), ITIL Certified
Alvin chew
Ranch Hand

Joined: Jan 08, 2004
Posts: 834
thanks for answering ..i using mySQL, so what is the best solution ? is it the one recommend by "java programmer" ? anyway, "java programmer" you have break the naming policy ..
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 60741
    
  65

Indeed "java programmer",

We're pleased to have you here with us on the Ranch, but there are a few rules that need to be followed, and one is that proper names are required. Please take a look at the JavaRanch Naming Policy and adjust your display name to match it.

In particular, your display name must be a first and a last name separated by a space character, and must not be obviously fictitious.

Thanks!
bear
Forum Bartender


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
zhao weiwei
Greenhorn

Joined: Nov 10, 2004
Posts: 1
if you use SQL Server 2k
you should do "select top 5 * from table_name order by clounm_name desc"
David Ulicny
Ranch Hand

Joined: Aug 04, 2004
Posts: 724
If you will use DB2 in future just use FETCH FIRST 5 ROWS ONLY


SCJP<br />SCWCD <br />ICSD(286)<br />MCP 70-216
Loren Rosen
Ranch Hand

Joined: Feb 12, 2003
Posts: 156
You also need to think about whether you want to deal with ties in the ranking (though in this particular case I suppose it's unlikely that two customers will have exactly the same number of transactions)
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: how to get top 5 result from database
 
Similar Threads
object oriented programming in javascript
Sharks in my mail box!
commit and rollback question
IT Consulting rate
Mixing direct SQL with HQL in the same transaction