Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Posts
    16

    Angry Unanswered: Last Question I Promise

    if i have if table of say :

    a1 a2 -- columns
    sam 1 -- values
    jim 2
    eric 5
    sam 1

    and i want to do an IF-THEN-ELSIF statement to select a single row from these where the first condition is if a1 has a higher number of identical entries than the rest of the table BUT if thats not true then by the number in a2 that is higher than all the other numbers BUT if that fails then just to randomly select a row by using the rows in a1. then in selected value of a1 by however of the 3 means it was selected by is to be put in to table NEXT. this is all to in in pl/sql

    how would this be done ?

    sorry to trouble you again but this bit is doing my head in !!!!

  2. #2
    Join Date
    Jan 2004
    Location
    Singapore
    Posts
    89

    use rank

    Try to make use of rank function available in oracle 9
    Thanks and Regards,

    Praveen Pulikunnu

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i realize that it is tough to make the mental leap from if-then record-at-a-time logic to set logic, but it is worth the effort

    some translations:

    "if a1 has a higher number of identical entries than the rest of the table" -- group by a1 and count the number of times each value of a1 occurs in the table

    "by the number in a2 that is higher than all the other numbers" -- for groups with the same number of occurrences, use the max(a2) value within the group

    "then just to randomly select a row" -- just take the row for the group

    result:
    Code:
    select a1
         , count(*) as rows
         , max(a2)  as maxa2
      from yourtable
    group
        by a1
    order 
        by rows desc
         , maxa2 desc
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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