OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » sql swap

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

sql swap [message #228440]

Tue, 03 April 2007 01:39

pavuluri
Messages: 247
Registered: January 2007

Senior Member

how can swap 4 and 7 row in a table ?

Thanks,
Srinivas

Report message to a moderator

Re: sql swap [message #228450 is a reply to message #228440]

Tue, 03 April 2007 02:02

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

There is no row 4 or 7 in a table.
In relational model, table is a heap there is no order.

Explain your problem instead of asking how to do what you think is the solution.

Regards
Michel

Report message to a moderator

Re: sql swap [message #228451 is a reply to message #228450]

Tue, 03 April 2007 02:07

pavuluri
Messages: 247
Registered: January 2007

Senior Member

asume that

i have emp table.

i want 7th row displayed as 5th row
and 5th row displayed as 7th row

thanks
srinivas

Report message to a moderator

Re: sql swap [message #228453 is a reply to message #228451]

Tue, 03 April 2007 02:08

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Once again there is no order, so there is no 5th or 7th row unless you yourself order your rows.
So use "order by".

Regards
Michel

(add first sentence)

[Updated on: Tue, 03 April 2007 02:09]

Report message to a moderator

Re: sql swap [message #228463 is a reply to message #228453]

Tue, 03 April 2007 02:37

pavuluri
Messages: 247
Registered: January 2007

Senior Member

thanks

working this query

select * from emp order by case when rownum=5 then 7
when rownum=7 then 5
else rownum end

thanks
srinivas

Report message to a moderator

Re: sql swap [message #228465 is a reply to message #228463]

Tue, 03 April 2007 02:47

pavuluri
Messages: 247
Registered: January 2007

Senior Member

with out order by
any other way
Thanks,
srinivas

Report message to a moderator

Re: sql swap [message #228468 is a reply to message #228465]

Tue, 03 April 2007 02:50

Frank
Messages: 7901
Registered: March 2000

Senior Member

We have been here time and time again.
Rows are returned in a more or less random order that is not predictable (at least not by 99.999% of us mortals) if you do not use an order by.
So, there is no point in swapping rows 4 and 7, because your results may change if you run the same query tomorrow.

Report message to a moderator

Re: sql swap [message #228482 is a reply to message #228465]

Tue, 03 April 2007 03:38

Maaher
Messages: 7065
Registered: December 2001

Senior Member

pavuluri wrote on Tue, 03 April 2007 09:47

with out order by
any other way
Thanks,
srinivas

No. YOU have to tell Oracle how you want it sorted. Why are you trying to avoid ORDER BY? Are you allergic to certain keywords?

MHE

Report message to a moderator

Re: sql swap [message #228492 is a reply to message #228482]

Tue, 03 April 2007 03:59

pavuluri
Messages: 247
Registered: January 2007

Senior Member

Thanks

Report message to a moderator

Re: sql swap [message #228502 is a reply to message #228492]

Tue, 03 April 2007 04:27

pavuluri
Messages: 247
Registered: January 2007

Senior Member

ok

asume that.

i want 1st row displayed as last row
and last row displayed as 1 st row

thanks
srinivas

Report message to a moderator

Re: sql swap [message #228506 is a reply to message #228502]

Tue, 03 April 2007 04:30

Maaher
Messages: 7065
Registered: December 2001

Senior Member

The same principle applies: You sort, for the second query (reverse order) you reverse the sort you used for the first query. That way, your 'first' record. will become your 'last' record.

MHE

[Updated on: Tue, 03 April 2007 04:31]

Report message to a moderator

Re: sql swap [message #228510 is a reply to message #228506]

Tue, 03 April 2007 04:36

pavuluri
Messages: 247
Registered: January 2007

Senior Member

i want only changed 1 and last records and between records same order
thanks
srinivas

Report message to a moderator

Re: sql swap [message #228512 is a reply to message #228510]

Tue, 03 April 2007 04:40

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

But which order.
THERE IS NO ORDER IN A TABLE UNTIL YOU GIVE ONE.
Once you give one YOU gives the number of each row, so you don't have to swap because you know which one is the first which one is the last and you already sort in the correct order.

Post an exemple of what you mean.

Regards
Michel

Report message to a moderator

Re: sql swap [message #228520 is a reply to message #228512]

Tue, 03 April 2007 05:00

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Michel and the others are so right.

I did a simple search on 'order' and 'paradigm' with my handle and here are some old threads:

My thoughts on sorting data. (I)
My thoughts on sorting data. (II)
My thoughts on sorting data. (III)
My thoughts on sorting data. (IV)

So one last time (feel free to join me): you need to use ORDER BY to sort data!

MHE

Report message to a moderator

Re: sql swap [message #228532 is a reply to message #228520]

Tue, 03 April 2007 05:24

pavuluri
Messages: 247
Registered: January 2007

Senior Member

Hi guys

see this

select e.*,rownum from emp e order by
case when rownum=(select max(rownum) from emp) then 1
when rownum=1 then (select max(rownum) from emp)
else rownum end

Thanks,
srinivas

Report message to a moderator

Re: sql swap [message #228533 is a reply to message #228532]

Tue, 03 April 2007 05:29

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

And you get an undeterministic result.

There is no difference with something like (without order):

with cnt as (select count(*) cnt from emp)
select e.*, decode(rownum,1,cnt,cnt,1,rownum) "ROWNUM"
from emp e, cnt
/

Regards
Michel

Report message to a moderator

Re: sql swap [message #228544 is a reply to message #228532]

Tue, 03 April 2007 06:00

Frank
Messages: 7901
Registered: March 2000

Senior Member

Ok, Step one: You DO believe us when we say that Oracle spits out rows in random order if you don't add an explicit order by on one of your columns?
Step two: What is the use of switching two rows if these rows are already in random order?

To accomplish your result (alternative to Michel's way):

SQL> select ename
  2  ,      empno
  3  ,      case when rownum = 1 then no_of_records
  4              when rownum = no_of_records then 1
  5              else rownum
  6         end my_rownum
  7  from   (select ename
  8          ,      empno
  9          ,      count(*) over () no_of_records
 10          from   emp
 11         );

ENAME           EMPNO  MY_ROWNUM
---------- ---------- ----------
SMITH            7369         14
ALLEN            7499          2
WARD             7521          3
JONES            7566          4
MARTIN           7654          5
BLAKE            7698          6
CLARK            7782          7
SCOTT            7788          8
KING             7839          9
TURNER           7844         10
ADAMS            7876         11
JAMES            7900         12
FORD             7902         13
MILLER           7934          1

[Updated on: Tue, 03 April 2007 06:01]

Report message to a moderator

Re: sql swap [message #228545 is a reply to message #228544]

Tue, 03 April 2007 06:07

pavuluri
Messages: 247
Registered: January 2007

Senior Member

u r query wrong

see this differnee

SELECT   e.*
       , ROWNUM
FROM     emp e
ORDER BY CASE
            WHEN ROWNUM = (SELECT MAX (ROWNUM)
                           FROM   emp)
               THEN 1
            WHEN ROWNUM = 1
               THEN (SELECT MAX (ROWNUM)
                     FROM   emp)
            ELSE ROWNUM
         END

EMPNO	ENAME	JOB		MGR	HIREDATE	SAL	COMM	DEPTNO	ROWNUM

7369	SMITH	CLERK		7902	12/17/1980	800		20	1
7499	ALLEN	SALESMAN	7698	2/20/1981	1600	300	30	2
7521	WARD	SALESMAN	7698	2/22/1981	1250	500	30	3
7566	JONES	MANAGER		7839	4/2/1981	2975		20	4
7654	MARTIN	SALESMAN	7698	9/28/1981	1250	1400	30	5
7698	BLAKE	MANAGER		7839	5/1/1981	2850		30	6
7782	CLARK	MANAGER		7839	6/9/1981	2450		10	7
7788	SCOTT	ANALYST		7566	4/19/1987	3000		20	8
7839	KING	PRESIDENT		11/17/1981	5000		10	9
7844	TURNER	SALESMAN	7698	9/8/1981	1500	0	30	10
7876	ADAMS	CLERK		7788	5/23/1987	1100		20	11
7900	JAMES	CLERK		7698	12/3/1981	950		30	12
7902	FORD	ANALYST		7566	12/3/1981	3000		20	13
7934	MILLER	CLERK		7782	1/23/1982	1300		10	14

[Mod-edit]Added code tags.

[Updated on: Tue, 03 April 2007 06:16] by Moderator

Report message to a moderator

Re: sql swap [message #228548 is a reply to message #228545]

Tue, 03 April 2007 06:08

Frank
Messages: 7901
Registered: March 2000

Senior Member

And exactly where is my query wrong?

Report message to a moderator

Re: sql swap [message #228576 is a reply to message #228544]

Tue, 03 April 2007 07:24

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

Step one: You DO believe us when we say that Oracle spits out rows in random order if you don't add an explicit order by on one of your columns?

No, I don't think the OP does really believe us. He sees the data coming back in exactly the same order each time does a query on his little demo tables,and assumes that watever we are talking about doesn't apply.

It's the only conclusion I can reach from the willful refusal to listen displayed on this thread.

Report message to a moderator

Re: sql swap [message #228577 is a reply to message #228548]

Tue, 03 April 2007 07:25

ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

see this differnce

I see absolutely no difference with the ordering. Both queries return the rows in random order.

( Threads like this can make you loose all hope in IT ( and humanity in general ) Wink

)

Report message to a moderator

Re: sql swap [message #228590 is a reply to message #228545]

Tue, 03 April 2007 07:48

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

pavuluri wrote on Tue, 03 April 2007 07:07

u r query wrong

see this differnee

You really need to go away for a long time and read the Concepts manual. You have no concept of a relational database.

Report message to a moderator

Re: sql swap [message #228609 is a reply to message #228590]

Tue, 03 April 2007 08:16

pavuluri
Messages: 247
Registered: January 2007

Senior Member

SQL> select ename
2 , empno
3 , case when rownum = 1 then no_of_records
4 when rownum = no_of_records then 1
5 else rownum
6 end my_rownum
7 from (select ename
8 , empno
9 , count(*) over () no_of_records
10 from emp
11 );

ENAME EMPNO MY_ROWNUM
---------- ---------- ----------
SMITH 7369 14
ALLEN 7499 2
WARD 7521 3
JONES 7566 4
MARTIN 7654 5
BLAKE 7698 6
CLARK 7782 7
SCOTT 7788 8
KING 7839 9
TURNER 7844 10
ADAMS 7876 11
JAMES 7900 12
FORD 7902 13
MILLER 7934 1

...................................................
SELECT e.*
, ROWNUM
FROM emp e
ORDER BY CASE
WHEN ROWNUM = (SELECT MAX (ROWNUM)
FROM emp)
THEN 1
WHEN ROWNUM = 1
THEN (SELECT MAX (ROWNUM)
FROM emp)
ELSE ROWNUM
END

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ROWNUM

7369 SMITH CLERK 7902 12/17/1980 800 20 1
7499 ALLEN SALESMAN 7698 2/20/1981 1600 300 30 2
7521 WARD SALESMAN 7698 2/22/1981 1250 500 30 3
7566 JONES MANAGER 7839 4/2/1981 2975 20 4
7654 MARTIN SALESMAN 7698 9/28/1981 1250 1400 30 5
7698 BLAKE MANAGER 7839 5/1/1981 2850 30 6
7782 CLARK MANAGER 7839 6/9/1981 2450 10 7
7788 SCOTT ANALYST 7566 4/19/1987 3000 20 8
7839 KING PRESIDENT 11/17/1981 5000 10 9
7844 TURNER SALESMAN 7698 9/8/1981 1500 0 30 10
7876 ADAMS CLERK 7788 5/23/1987 1100 20 11
7900 JAMES CLERK 7698 12/3/1981 950 30 12
7902 FORD ANALYST 7566 12/3/1981 3000 20 13
7934 MILLER CLERK 7782 1/23/1982 1300 10 14

.............................................
You have no concept of a relational database.

thanks
srinivas

Report message to a moderator

Re: sql swap [message #228611 is a reply to message #228609]

Tue, 03 April 2007 08:19

Frank
Messages: 7901
Registered: March 2000

Senior Member

Could you please explain what your nice construct does?
What if you remove that and just do a select *, rownum from emp?

Report message to a moderator

Re: sql swap [message #228613 is a reply to message #228609]

Tue, 03 April 2007 08:21

pavuluri
Messages: 247
Registered: January 2007

Senior Member

sorry

see diff

SQL> select ename
2 , empno
3 , case when rownum = 1 then no_of_records
4 when rownum = no_of_records then 1
5 else rownum
6 end my_rownum
7 from (select ename
8 , empno
9 , count(*) over () no_of_records
10 from emp
11 );

ENAME EMPNO MY_ROWNUM
---------- ---------- ----------
SMITH 7369 14
ALLEN 7499 2
WARD 7521 3
JONES 7566 4
MARTIN 7654 5
BLAKE 7698 6
CLARK 7782 7
SCOTT 7788 8
KING 7839 9
TURNER 7844 10
ADAMS 7876 11
JAMES 7900 12
FORD 7902 13
MILLER 7934 1
...........................
my query
and result

select e.*,rownum from emp e order by case when rownum=(select max(rownum) from emp) then 1
when rownum=1 then (select max(rownum) from emp)
else rownum end

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ROWNUM

7934 MILLER CLERK 7782 1/23/1982 1300 10 14
7499 ALLEN SALESMAN 7698 2/20/1981 1600 300 30 2
7521 WARD SALESMAN 7698 2/22/1981 1250 500 30 3
7566 JONES MANAGER 7839 4/2/1981 2975 20 4
7654 MARTIN SALESMAN 7698 9/28/1981 1250 1400 30 5
7698 BLAKE MANAGER 7839 5/1/1981 2850 30 6
7782 CLARK MANAGER 7839 6/9/1981 2450 10 7
7788 SCOTT ANALYST 7566 4/19/1987 3000 20 8
7839 KING PRESIDENT 11/17/1981 5000 10 9
7844 TURNER SALESMAN 7698 9/8/1981 1500 0 30 10
7876 ADAMS CLERK 7788 5/23/1987 1100 20 11
7900 JAMES CLERK 7698 12/3/1981 950 30 12
7902 FORD ANALYST 7566 12/3/1981 3000 20 13
7369 SMITH CLERK 7902 12/17/1980 800 20 1

see difference. no difference ok

i have no concept of a relational database

Report message to a moderator

Re: sql swap [message #228615 is a reply to message #228613]

Tue, 03 April 2007 08:27

pavuluri
Messages: 247
Registered: January 2007

Senior Member

hi
frank

u r not swaping only row num is swaping, see ur query result.

see my query result row is swaping.

Thanks,
srinivas

Report message to a moderator

Re: sql swap [message #228617 is a reply to message #228615]

Tue, 03 April 2007 08:39

Maaher
Messages: 7065
Registered: December 2001

Senior Member

would you please ADD CODE TAGS! Every time you place code, be it a select or a result set. use these tags: [CODE][/CODE] to preserve formatting.
Add an ORDER BY in your query and "it will be swapped".

I'm tired of repeating myself but: add an order by! But don't take our word for it.

MHE

[Updated on: Wed, 04 April 2007 01:09]

Report message to a moderator

Re: sql swap [message #228618 is a reply to message #228615]

Tue, 03 April 2007 08:40

Frank
Messages: 7901
Registered: March 2000

Senior Member

SQL> select e.*,rownum from emp e order by case when rownum=(select max(rownum) from emp) then 1
  2  when rownum=1 then (select max(rownum) from emp)
  3  else rownum end;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO     ROWNUM
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10         14
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30          2
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30          3
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20          4
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30          5
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30          6
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10          7
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20          8
      7839 KING       PRESIDENT            17-NOV-81       5000                    10          9
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30         10
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20         11
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30         12
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20         13
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20          1

Now we get further into time and someone changes something toi your table:

SQL> create index my_index on emp(empno desc, ename, job, mgr, hiredate, sal, comm, deptno);

Index created.

SQL> analyze table emp compute statistics for table for all indexes;

Table analyzed.

SQL> select e.*,rownum from emp e order by case when rownum=(select max(rownum) from emp) then 1
  2  when rownum=1 then (select max(rownum) from emp)
  3  else rownum end;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO     ROWNUM
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20         14
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20          2
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30          3
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20          4
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30          5
      7839 KING       PRESIDENT            17-NOV-81       5000                    10          6
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20          7
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10          8
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30          9
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30         10
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20         11
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30         12
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30         13
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10          1

14 rows selected.

This is what we mean. You HAVE to use an order by to guarantee the order of rows returned.

Report message to a moderator

Re: sql swap [message #228620 is a reply to message #228615]

Tue, 03 April 2007 08:44

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

As you don't give any order rows are not swapping just numbers.
So any query that swaps number fits your requirement.

You seems to do not understand that there is NO ORDER until you give an ORDER BY clause.
If you don't give an order then any order is valid.
And so any query that returns "14, 2, 3, ..., 13, 1" with any data in front of these numbers is valid.

You will tell me you gave an ORDER BY clause but your order does not order data, it orders only rows (not data) in your result set. Maybe a too complex concept for you.

Regards
Michel

Report message to a moderator

Re: sql swap [message #228621 is a reply to message #228590]

Tue, 03 April 2007 08:50

ebrian
Messages: 2794
Registered: April 2006

Senior Member

joy_division wrote on Tue, 03 April 2007 08:48

You really need to go away for a long time and read the Concepts manual. You have no concept of a relational database.

C'mon joy_division...the OP is having a hard to reading the sticky How to format your posts and you want him to read over 500 pages for the Concepts manual?

Report message to a moderator