Home » SQL & PL/SQL » SQL & PL/SQL » help needed
help needed [message #220252] Mon, 19 February 2007 21:51 Go to next message
geturchandu
Messages: 8
Registered: July 2006
Junior Member
I will explain my problem in short. I have a set of results from a select statement. From the results obtained i want to interchange the even and odd positioned data values.

ie data in the 1'st row should be moved to 2'nd and data from 2'nd should be moved to 1'st. data in 3'rd to 4'th and the 4'th to 3'rd and so on.

select emp_id from employee where dept_id=30;
say we got the following results back

empid
101
102
103
104
105
106


After interchanging the result should be as follows

empid
102
101
104
103
106
105


Can anyone tell me how to get this done.



Re: help needed [message #220268 is a reply to message #220252] Tue, 20 February 2007 00:00 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
are there any gaps?
If not, you could use an order by, in which you would add 1.5 to odd numbers.

If there are gaps, you first have to determine the 'natural' order by using row_number() over (), and then do the same trick on this pseudo column

[Updated on: Tue, 20 February 2007 00:02]

Report message to a moderator

Re: help needed [message #220281 is a reply to message #220252] Tue, 20 February 2007 00:42 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
First, without stating ORDER BY your initial result is NOT sorted. So I sorted it by emp_id. Then you can "re-order" the rows like this:
SELECT emp_id 
FROM (SELECT emp_id,
    case when mod(rownum, 2) = 0 then rownum-1 else rownum+1 end rn
 FROM (SELECT emp_id
  FROM employee
  WHERE dept_id=30
  ORDER BY emp_id))
ORDER BY rn;

Analytics might fit better:
SELECT emp_id 
FROM (SELECT emp_id, ROW_NUMBER() OVER (ORDER BY emp_id) rn
 FROM employee
 WHERE dept_id=30)
ORDER BY case when mod(rn, 2) = 0 then rn-1 else rn+1 end;

Re: help needed [message #220283 is a reply to message #220252] Tue, 20 February 2007 00:54 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
hi,
plz see the query
select * from (select empno, (case when r=1 then 2
when r=2 then 1
when r=3 then 4
when r=4 then 3

end)rn from (select empno, row_number() over (order by empno) r from emp) where r<=4)
order by rn asc

Thanks,
srinivas
Re: help needed [message #220291 is a reply to message #220283] Tue, 20 February 2007 01:58 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Out of curiosity, would you have suggested that as a solution if there were 10,000 rows returned by the query?

You can use analytics for this:
select emp_id
      ,ename
      ,row_number() over (order by emp_id) rnum
from   emp_test
order by floor((rnum+1)/2),mod(rnum,2);

     EMPID ENAME                                RNUM
---------- ------------------------------ ----------
         2 Employee 2                              2
         1 Employee 1                              1
         4 Employee 4                              4
         3 Employee 3                              3
         6 Employee 6                              6
         5 Employee 5                              5
         8 Employee 8                              8
         7 Employee 7                              7
        10 Employee 10                            10
         9 Employee 9                              9
Previous Topic: execution of procedure in trigger
Next Topic: CLCB to PDF File
Goto Forum:
  


Current Time: Wed Dec 07 20:16:34 CST 2016

Total time taken to generate the page: 0.10739 seconds