Home » Other » Training & Certification » Reverse the order of column
Reverse the order of column [message #290297] Fri, 28 December 2007 05:33 Go to next message
neelabh_SE
Messages: 20
Registered: October 2007
Junior Member

Hello,

I have a table with varchar2 column,say 'abc'.
The task is to create a new table same as above but to reverse the order of “abc” column with first value being replaced with last,second replaced by second-last value so on and so forth.

Please suggest me a way ahead or approach to follow.


thanks,
neel
Re: Reverse the order of column [message #290301 is a reply to message #290297] Fri, 28 December 2007 05:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Define first?
Do you want to change the order of the columns or do you want do change the order of values in a column?
Post a test case, what you have and what you want.

Regards
Michel

[Updated on: Fri, 28 December 2007 05:41]

Report message to a moderator

Re: Reverse the order of values in column [message #290307 is a reply to message #290297] Fri, 28 December 2007 05:54 Go to previous messageGo to next message
neelabh_SE
Messages: 20
Registered: October 2007
Junior Member

Well, I want to reverse the values in column.

Test Case:

Present Scenario :

"zzz" table has below data

ID Name
40000284 Hetlyn
40000285 Bosley
.
.
40030133 Doooley
40030134 Bandy

Expected Solution :

"zzz1",a new table with same structure as "zzz" but reversed value in column Name. Below is the example:

ID Name
40000284 Bandy
40000285 Doooley
.
.
40030133 Bosley
40030134 Hetlyn


thanks,
neel

Re: Reverse the order of column [message #290308 is a reply to message #290297] Fri, 28 December 2007 05:57 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

What about the value of column ID? It remains same?

Kiran.
Re: Reverse the order of column [message #290310 is a reply to message #290297] Fri, 28 December 2007 06:00 Go to previous messageGo to next message
neelabh_SE
Messages: 20
Registered: October 2007
Junior Member
Yes, the column "ID" should remain same.

neel
Re: Reverse the order of column [message #290311 is a reply to message #290297] Fri, 28 December 2007 06:05 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

You can use ORDER BY ROWID DESC. But i don't know how do you keep ID value as same.

Kiran
Re: Reverse the order of column [message #290319 is a reply to message #290297] Fri, 28 December 2007 06:17 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
I would rather ask: what about ID, is it unique?
If so, analytics will give you the desired result
SELECT t1.id, t2.name
FROM
 (SELECT id, ROW_NUMBER() OVER (ORDER BY id) rn
  FROM zzz) t1,
 (SELECT name, ROW_NUMBER() OVER (ORDER BY id DESC) rn
  FROM zzz) t2
WHERE t1.rn = t2.rn;
Re: Reverse the order of column [message #290324 is a reply to message #290297] Fri, 28 December 2007 06:42 Go to previous messageGo to next message
neelabh_SE
Messages: 20
Registered: October 2007
Junior Member

Thanks a lot,the below query worked perfectly to solve my case.

thanks again for all the help.

neel

(p.s.-Flyboy,yes the id is unique.)
Re: Reverse the order of column [message #290325 is a reply to message #290297] Fri, 28 December 2007 06:47 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

Ok, thats good.

Kiran.
Re: Reverse the order of column [message #290329 is a reply to message #290324] Fri, 28 December 2007 06:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@neelabh_SE,

I hope you will tell your teacher you got the answer on the web and not by your work.

Regards
Michel
Re: Reverse the order of column [message #290343 is a reply to message #290297] Fri, 28 December 2007 07:20 Go to previous messageGo to next message
convey05
Messages: 43
Registered: December 2007
Location: CHENNAI
Member
hi,
you can do like this also

insert into test_dummy_2
select parent from
(select rownum,parent from test_dummy_1
order by rownum desc)a
icon12.gif  Re: Reverse the order of column [message #290354 is a reply to message #290297] Fri, 28 December 2007 08:00 Go to previous messageGo to next message
neelabh_SE
Messages: 20
Registered: October 2007
Junior Member
@Michel, yes definitely after all "Honest is the biggest virtue"..
Smile
Re: Reverse the order of column [message #290358 is a reply to message #290297] Fri, 28 December 2007 09:05 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Oh, I could suspect this is homework as it is of a little real use.
I would describe used steps in words, but was too lazy to do so.
At least I hope you know what analytics is and understand how is it used in this solution.
Previous Topic: Can any one solve this
Next Topic: OCP - Exams
Goto Forum:
  


Current Time: Tue Dec 06 08:40:01 CST 2016

Total time taken to generate the page: 0.09966 seconds