wood burning stoves
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
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: 63852

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: http://aspose.com/file-tools
subject: how to get top 5 result from database
It's not a secret anymore!