1

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?

Michael Pakhantsov
  • 24,855
  • 6
  • 60
  • 59
nishant
  • 112
  • 1
  • 3
  • 10

3 Answers3

5

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)
mathewb
  • 155
  • 9
il_guru
  • 8,383
  • 2
  • 42
  • 51
  • +1, the only answer so far that doesn't bother with an outer select. – Marcelo Cantos Nov 02 '10 at 08:49
  • There is one problem in this, It gives me duplicate entries also. One result might appear multiple times. What is the solution – nishant Nov 27 '10 at 10:47
  • Mmm... UNION by default eliminate duplicates. The only reason i see here is that a title contains both India and Australia in it and ar not excluded because of the added ORD column. One possible solution could be to slightly change one of the query to exclude results from the other one. For example you can change the where in the second query to ' where title like %Australia% and not like %india% '. – il_guru Nov 29 '10 at 10:47
  • @il_guru Actually I am trying to search multiple keywords in database. After splitting the keywords I have to run lot of queries depending upon number of keywords. adding a not like option for all unions will make queries complex.- – nishant Dec 07 '10 at 10:25
  • @il_guru Query- select *, 1 as ORD from discussion where ( disText like '%India%' AND disText like '%Australia%' AND disText like '%Canada%' ) UNION select *, 2 as ORD from discussion where ( disText like '%India%' AND disText like '%Australia%') UNION select *, 3 as ORD from discussion where ( disText like '%Australia%' AND disText like '%Canada%') UNION select *, 4 as ORD from discussion where ( disText like '%Canada%' AND disText like '%India%') UNION select *, 5 as ORD from discussion where ( disText like '%India%' OR disText like '%Australia%' OR disText like '%Canada%' ). – nishant Dec 07 '10 at 10:32
1

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
Michael Pakhantsov
  • 24,855
  • 6
  • 60
  • 59
0
select * from
(
select * from discussion where title like '%india%' 
UNION 
select * from discussion where title like '%Australia%'
)
ORDER BY title DESC
;
anishMarokey
  • 11,279
  • 2
  • 34
  • 47
0xCAFEBABE
  • 5,576
  • 5
  • 34
  • 59