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

    Unanswered: Using a pl-sql procedure or cursor to select top 3 rank

    Hi all,

    can someone please help me to select top 3 ranks from input data.

    The customer, make and zipcode will be passed into the procedure or cursor at runtime. so thinking either procedure or cursor might be needed along with using dense_rank function.

    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


    expected output:

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


    Thanks

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

    Cool Rtfm

    If you would Read The Fine Manual Oracle® Database SQL Language Reference
    you may find the analytical functions: "RANK()", "DENSE_RANK()" and "ROW_NUMBER()" which could solve your problem.
    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
  •