File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases 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 REST with Spring (video course) this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "how to get top 5 result from database" Watch "how to get top 5 result from database" New topic

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: 1082

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

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

Joined: Jan 10, 2002
Posts: 63544

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.

Forum Bartender

[Asking smart questions] [About Bear] [Books by Bear]
zhao weiwei

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)
I agree. Here's the link:
subject: how to get top 5 result from database
It's not a secret anymore!