Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Posts
    3

    Smile 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
    5 Bread 2
    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. #2
    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.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Jan 2004
    Posts
    3

    Thumbs up

    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
  •