This discussion is archived
6 Replies Latest reply: Sep 24, 2012 1:21 AM by francy77 RSS

order of result set

francy77 Newbie
Currently Being Moderated
Hi all,
i need to write a query that the result set is as i expected and depende by the input.
In other word:

if i run this query:
select employee_id,first_name,last_name from EMPLOYEES where last_name in ('Hall','Vargas','Tucker')
i want to get the following result:
152    Peter    Hall
144    Peter    Vargas
150    Peter    Tucker
and if i change the order in the IN clause, like this one (changing Hall from the beginning to the last position)
select employee_id,first_name,last_name from EMPLOYEES where last_name in ('Vargas','Tucker','Hall')
getting this result in this order:
144    Peter    Vargas
150    Peter    Tucker
152    Peter    Hall
is there a way to accomplish this?

Francesco
  • 1. Re: order of result set
    odie_63 Guru
    Currently Being Moderated
    Similar thread : {thread:id=2393860}
  • 2. Re: order of result set
    Purvesh K Guru
    Currently Being Moderated
    One way of doing so!!!
     select employee_id,first_name,last_name from EMPLOYEES where last_name in ('Vargas','Tucker','Hall') order by decode(last_name, 'Vargas', 1, 'Tucker', 2, 'Hall', 3);
  • 3. Re: order of result set
    Stew Ashton Expert
    Currently Being Moderated
    with indata as (
      select 'Vargas,Tucker,Hall' x, ',' delim from dual
    ), instr AS (
      SELECT rownum rn, trim(SUBSTR(
        x,
        (case level
          when 1 then 1 
          else instr(x, delim, 1, level-1) + 1 
        end),
        (case instr(x, delim, 1, level)
          when 0 then 4000
          else instr(x, delim, 1, level) - 1 -
            (case level
              when 1 then 0
              else instr(x, delim, 1, level-1) 
            end)
        end)
      )) AS token
      FROM indata
      CONNECT BY level <= LENGTH(x) - LENGTH(REPLACE(x, delim, '')) + 1
    )
    select employee_id,first_name,last_name
    from EMPLOYEES a, instr b
    where a.last_name = b.token
    order by b.rn;
    
    EMPLOYEE_ID FIRST_NAME           LAST_NAME               
    ----------- -------------------- -------------------------
            144 Peter                Vargas                    
            150 Peter                Tucker                    
            152 Peter                Hall
  • 4. Re: order of result set
    rp0428 Guru
    Currently Being Moderated
    Same question in this other forum and thread. Do you work with this other poster?
    Re: How do you keep the order using SELECT WHERE IN()?
  • 5. Re: order of result set
    Frank Kulash Guru
    Currently Being Moderated
    Hi, Francesco,

    I like Stew's solution, above.

    Here's another approach, that might better in some particular situations:
    DEFINE      last_name_list = "'Hall','Vargas','Tucker'"
    
    
    SELECT    employee_id, first_name, last_name 
    FROM        hr.employees 
    WHERE        last_name IN (&last_name_list)
    ORDER BY  INSTR ( Q'{&last_name_list}'
                      , '''' || last_name || ''''
              )
    ;
    This assumes that you know some character (I used '}' above) that never occurs at the very end of last_name.
  • 6. Re: order of result set
    francy77 Newbie
    Currently Being Moderated
    I think this is the simpler way of doing that.
    Thanks

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points