Home » SQL & PL/SQL » SQL & PL/SQL » Need special sort for a nation table (Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit)
Need special sort for a nation table [message #425092] Wed, 07 October 2009 08:08 Go to next message
MarkusW
Messages: 20
Registered: June 2009
Location: Austria
Junior Member
hi,

need help for special sorting of a nation table.

id name
-----------------------
1 Frankreich
3 Niederlande
4 Deutschland
5 Italien
6 Grossbritannien
7 Irland
8 Dänemark
9 Zypern
10 Albanien

My job is to get a select, where the nation "Deutschland" is in the first row, all other nations are sorted by name, my result should like this

id name
------------------------
4 Deutschland
10 Albanien
8 Dänemark
1 Frankreich
6 Grossbritannien
5 Italien
7 Irland
3 Niederlande
9 Zypern


i try it with following select

select id, replace(name, 'AA', '') as name from
(
select id, ('AA' || name) as name from nation
where name like 'Deutschland%'
union
select id, name from nation
where name not like 'Deutschland%'
order by name
)

I thinks, this select is not very fast and a little hack, is there a better possibility to sort the nations with a case-when in the order clause or with an other SQL technique (group by or an order by with an special order algorithm?


thx 4 any help!

Mark

Re: Need special sort for a nation table [message #425093 is a reply to message #425092] Wed, 07 October 2009 08:13 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
order by decode(nation,'Deutschland',0,1), nation

Quote:
thx 4 any help!

Do NOT use IM/SMS speak.

Regards
Michel

[Updated on: Wed, 07 October 2009 08:14]

Report message to a moderator

Re: Need special sort for a nation table [message #425096 is a reply to message #425093] Wed, 07 October 2009 08:49 Go to previous message
MarkusW
Messages: 20
Registered: June 2009
Location: Austria
Junior Member
Hi Michel,

thanks for this quick help, a simple solution, you are great!

Regards
Markus
Previous Topic: Need opinion in using TO_CHAR
Next Topic: merging row in a query
Goto Forum:
  


Current Time: Sun Sep 25 02:40:45 CDT 2016

Total time taken to generate the page: 0.13806 seconds