6 Replies Latest reply: Sep 24, 2012 3:21 AM by francy77 RSS

    order of result set

    francy77
      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
          Similar thread : {thread:id=2393860}
          • 2. Re: order of result set
            Purvesh K
            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
              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
                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
                  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
                    I think this is the simpler way of doing that.
                    Thanks