Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2005
    Posts
    92

    Unanswered: select best offer

    Can someone please help me to select top 2 ranks from input data and find the sum of top 2 offers.

    I assume using dense_rank function where rank <=2 will give me top 2 offers.

    I am also looking to get 'total_offer' which is sum of offer1 and offer2. when there is no offer 2 ( eg:taurus) total offer is offer1 and 'null' for offer 2 in the output.

    input:

    customer make zipcode offer
    mark focus 101 250
    mark focus 101 2500
    mark focus 101 1000
    mark focus 101 1500
    henry 520i 21405 500
    henry 520i 21405 100
    henry 520i 21405 750
    henry 520i 21405 100
    mark taurus 48360 250
    mark mustang 730 500
    mark mustang 730 1000
    mark mustang 730 1250


    Desired Output:

    | CUSTOMER | MAKE | ZIPCODE | TOP_OFFER1 | TOP_OFFER2 | Total_offer |
    |----------|---------|---------|-------|------| ---------------|
    | henry | 520i | 21405 | 750 | 500 | 1250
    | mark | focus | 101 | 2500 | 1500 | 4000
    | mark | mustang | 730 | 1250 | 1000 | 2250
    | mark | taurus | 48360 | 250 | NULL| 250 |
    Last edited by pavan_test; 07-27-15 at 15:53.

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool Nice homework assignment.

    You are right, you can use the dense_rank function.
    What have you coded?
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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