...

View Full Version : Resolved union order by



BubikolRamios
02-15-2009, 04:33 PM
data about currencyies


OTHER1 null
USD 2
EUR 1
OTHER2 null
OTHER3 null
.........


the desired result



EUR 1
USD 2
OTHER1 null
OTHER2 null
OTHER3 null
.........


this does not work:


select col1,col2 from tab where col2 is not null order by col2
union
select col1,col2 from tab where col2 is null order by col1


any suggestion ?

bazz
02-15-2009, 04:39 PM
what result did you get?

bazz

BubikolRamios
02-15-2009, 04:39 PM
no result coz it is illegal to use order by inside union, can be only at the end.

the solution is invention of new field


select col1,col2,1 as z_field from tab where col2 is not null
union
select col1,col2,2 as z_field from tab where col2 is null
order by z_field,col2,col1

guelphdad
02-15-2009, 07:41 PM
why do you need a union at all?

look into the use of ORDER BY FIELD

BubikolRamios
02-15-2009, 08:20 PM
coz it wouldnt work, example:



EUR 2
USD 1
other1 null


order by col2,col1 --> would bring null on top,
and if I go then with by col2 desc, col1 --> not good either

Old Pedant
02-15-2009, 08:41 PM
But there is an easy solution for that:


select col1, col2 FROM table
order by IF(col2 IS NULL, 2, 1 ), col2, col1

That's doing essentially the same thing your union-with-added-field is doing but without the need for the added field or the union.

guelphdad
02-15-2009, 09:21 PM
coz it wouldnt work

yes it would work without the union, you just don't know how. As I said search on ORDER BY FIELD which is your solution and doesn't need a UNION.

Old Pedant
02-15-2009, 09:36 PM
I think he understood ORDER BY FIELD. He showed that by his use of the z_field in the ORDER BY involving a UNION. What he didn't consider was putting a conditional (e.g., IF or CASE) in the ORDER BY.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum