CODEWITHSUNDEEP
sqlsql-order-byunionunion-all
nishant
nishant

Reputation: 112

UNION Query-display results in order queries are written

example - 

select * from discussion where title like '%india%' 
UNION 
select * from discussion where title like '%Australia%'

It shows me results in order of discussion IDs mixing both typse of results

I want to display India results first then Australia's results and I cant use Option ALl as I need to remove duplicate rows also.

What should be done?

Upvotes: 1

Views: 2238

Answers (3)

il_guru
il_guru

Reputation: 8508

You could add a column to order on

select *, 1 as ORD from discussion where title like '%india%' 
UNION 
select *, 2 as ORD from discussion where title like '%Australia%'

order by ORD

EDIT - 29/11/2010

Due to the duplicate with ORD problem i was thinking about a, maybe, more elegant way to achive this

Select * from discussion
where title like '%india%' or title like '%Australia%'
order by (case when title like '%india%'then 1 else 2 end)

Upvotes: 5

0xCAFEBABE
0xCAFEBABE

Reputation: 5666

select * from
(
select * from discussion where title like '%india%' 
UNION 
select * from discussion where title like '%Australia%'
)
ORDER BY title DESC
;

Upvotes: 0

Michael Pakhantsov
Michael Pakhantsov

Reputation: 25370

Try:

SELECT * FROM
(
  select 1 OrderNo, d.* from discussion d where title like '%india%' 
  UNION 
  select 2 OrderNo, d.* from discussion d where title like '%Australia%'
)
ORder by OrderNo

Upvotes: 1

Related Questions