union order by

[BubikolRamios]

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]

what result did you get?

bazz

[BubikolRamios]

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]

why do you need a union at all?

look into the use of ORDER BY FIELD

[BubikolRamios]

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]

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]

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]

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.