# Thread: Sort ResultSet and return the top 100 records

1. Registered User
Join Date
Jan 2004
Posts
3

## Unanswered: Sort ResultSet and return the top 100 records

I have a very complicated algorithm to sort the customer record based on their status. Each customer has been given a random number in case of two customers has the same status; we can use the random number to sort them. The goal is to order the customer by rank and assigned a position.

My customer population is around 50,000. And, each customer will be rank 1 to 50,000. I am able to use the Case statement in Sql to rank the customer and order them by the random number.

The problems I have is
1. My web page cannot handle to display 50,000 at one time. I need to return 100 records at a time to display. I realized that in oracle a simple table likes this
Customer
Customer_id Name Rank
1 Apple 2
2 Orange 1
4 Egg 1
3 Pear 1
Select Customer_id from customer where rownum = 3 order by rank, customer_id

Oracle will return Orange, Egg, Apple NOT the whole sorting Orange, Pear, Egg.

Therefore, I need to return the whole 50,000 every time from the database in order to sort customer correctly. (I can't really create a temp table because customer status can change from hour to hour and their rank changed).

My get away form this is to use the max customer id to limited my population on the next query. i.e. select customer_id from customer
where customer_id > 3 order by rank, customer_id

Does anyone have a better solution than mine on this situation?

2. I also want to display the Position the Customer is in.
i.e the above table Pear should have position 3. I want to append that during my select statement. But, try RowID, rownum and other method doesn't work well Oracle keeps return the records position not the sort order position. I think of appending the position after i get the resultset in a stored procedure. Any other suggestions?

Any help or idea or advice would be appreciated!

2. Registered User
Join Date
Sep 2003
Location
The extremely Royal borough of Kensington, London
Posts
778
select id, name, rank, rownum as POS
from
(select id, name, rank
from customer
order by rank, id);

Please note, that rownum = x, where x > 1 will always evaluate to false.

3. Registered User
Join Date
Jan 2004
Posts
3
Thanks. It is a bit slow using this method (like it takes a minutes for the result to come back). But, it works. THanks.

Originally posted by r123456
select id, name, rank, rownum as POS
from
(select id, name, rank
from customer
order by rank, id);

Please note, that rownum = x, where x > 1 will always evaluate to false.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•