02-15-2009, 05:33 PM
data about currencyies
the desired result
this does not work:
select col1,col2 from tab where col2 is not null order by col2
select col1,col2 from tab where col2 is null order by col1
any suggestion ?
what result did you get?
02-15-2009, 05: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
select col1,col2,2 as z_field from tab where col2 is null
order by z_field,col2,col1
02-15-2009, 08:41 PM
why do you need a union at all?
look into the use of ORDER BY FIELD
02-15-2009, 09:20 PM
coz it wouldnt work, example:
order by col2,col1 --> would bring null on top,
and if I go then with by col2 desc, col1 --> not good either
02-15-2009, 09: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.
02-15-2009, 10: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.
02-15-2009, 10: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.