Order by clause.

[patilabhijeetj]

Hi all,
I have one query related to order by clause.
For example, consider a table containing all the names of cities like London, Munich, Singapur, LasVegas, Washington, Tokya & Other.

How to write a order by clause of a query so that when I say "SELECT * FROM CITY" and some ORDER BY clause on column "CITY_NAME" and the result should be

LASVEGAS
LONDON
MUNICH
SINGAPUR
TOKYA
OTHER

Please note that in all the cases OTHER must be the last record.

Please forgive me incase anyone feels this as a stupid question.

Sincerely,
AJP.

[yogeshsr11]

If you need distinct rows........
select city_name
from city
where upper(ltrim(rtrim(city_name))) not like upper('other')
order by city_name
union
select city_name from city
where upper(ltrim(rtrim(city_name))) like upper('other')
If you need all rows........
select city_name
from city
where upper(ltrim(rtrim(city_name))) not like upper('other')
order by city_name
union all
select city_name from city
where upper(ltrim(rtrim(city_name))) like upper('other')

[Leeli]

I am on a home machine with Oracle 10G Express and attempted this suggested query to get an ordering. I referenced the Locations table on the HR schema and I have found I can't use the order by clause in the query, getting the error " SQL command not properly ended". Incidentally I am using Oracle Application Express (formerly HTML DB). I also tried SQL Plus command line and got the same error.

If I take out the Order by, I don't get the error but then I don't get an ordered list. However, if I use Union All, it will put 'Seattle' last.

This is my query which gets an error-
select city
from locations
where city not like 'Seattle'
order by city
union
select city
from locations
where city like 'Seattle'

If I don't want to use Union All, my workaround for an ordering is:
select '1', city
from locations
where city not like 'Seattle'
union
select '2', city
from locations
where city like 'Seattle'

Does anyone know is this behavior specific to 10g?

Thank you, I know this thread is somewhat old.

[Methos]

select city
from locations
where city not like 'Seattle'
union
select city
from locations
where city like 'Seattle'
order by city

[stecal]

Umm, did you?

SQL> select city
2 from locations
3 where city not like 'Seattle'
4 union
5 select city
6 from locations
7 where city like 'Seattle'
8 order by city;

CITY
------------------------------
Beijing
Bern
Bombay
Geneva
Hiroshima
London
Mexico City
Munich
Oxford
Roma
Sao Paulo
Seattle
Singapore
South Brunswick
South San Francisco
Southlake
Stretford
Sydney
Tokyo
Toronto
Utrecht
Venice
Whitehorse

23 rows selected.

[Leeli]

Yes, it does work that way without error; however, then it doesn't order as was wanted above with 'Other' or 'Seattle' last...

[sharad_nisheeth]

SELECT NVL(NAME,'OTHER') FROM (SELECT DECODE(NAME,'OTHER',NULL,NAME) NAME FROM CITY ORDER BY NAME ASC NULLS LAST);


I do the sort first by replacing OTHER occurances by NULL and keeping NULLS at last. And the translate them back to OTHER once things are in order and sorted as per the requirements.

Write me at sharad_nisheeth@yahoo.co.in , immediately if u think u agree with the solution or in case u disagree write me the reason.

Cheers!!!

Sharad

[suchetar]

How about this?

select city from locations
order by
decode(city, 'Other','ZzOther', city)