Order by within Union

[muppet]

I have a query which generates an xml file from data collected. There are 2 columns in the data (reason, TotTime) but they are joined together into one column using the concat command. Currently the results are ordered by reason, but I want to limit my results to only the top 5 TotTime, so I want to put in a 'order by TotTime desc limit 5' somewhere but I just cannot get it to accept the changes.

Here is the query I am using:

select "<chart caption='Downtime' baseFontSize='15'  fontColor='555555' bold='1'  bgColor='F3F3F3'>" as error, '' as TotTime

	union

select Concat("<set label='",reason,"'value='", sum(TotTime),"' />"), '' as reason
		from(


SELECT reason, truncate((sec_to_time(SUM(TIME_TO_SEC(lost_time)))/60), 1) as 'TotTime'
	FROM abm_downtime
	where lost_time != ''
	and 			
		(
		case
			when (time(timestamp) between '07:20:00' and '15:19:59') then 'day'
			when (time(timestamp) between '15:20:00' and '23:19:59') then 'swing'
			when (time(timestamp) between '23:20:00' and '07:19:59') then 'night'
		end
	=
		case
			when (time(now()) between '07:20:00' and '15:19:59') then 'day'
			when (time(now()) between '15:20:00' and '23:19:59') then 'swing'
			when (TIME(NOW()) BETWEEN '23:20:00' and '23:59:59') OR (TIME(NOW()) BETWEEN '00:00:00' AND '07:19:59') then 'night'
		end
		)
	and machine = 'abm5'

	and date(timestamp) = date(now())
  group by reason

)
as musthavename where reason != ''
group by reason 

union
select '</chart>' as reason, '' as TotTime

and here is the output:

'<chart caption='Downtime' baseFontSize='15'  fontColor='555555' bold='1'  bgColor='F3F3F3'>', ''
'<set label='LEFT DIRECT SEAL HEAT ALARM'value='1.7' />', ''
'<set label='LEFT LIGHT CURTAIN TRIP'value='18.5' />', ''
'<set label='LEFT SPOUT NOT IN PLACE'value='3.4' />', ''
'<set label='LEFT TAP INSERTER DID NOT PLACE A TAP'value='0.9' />', ''
'<set label='RIGHT LIGHT CURTAIN TRIP'value='6.7' />', ''
'<set label='RIGHT TAP INSERTER DID NOT PLACE A TAP'value='3.7' />', ''
'</chart>', ''

but as you can see the order of the results is by reason, not totTime. How can I change the order?

[smantscheff]

You would need a limit clause in your subquery, but MySQL doesn't support this yet.
And since you are mixing logic and layout anyway, maybe you would be better off using a procedural language to display your query results.
What do you mean by but

I just cannot get it to accept the changes.

? What is the error message?

[muppet]

Incorrect usage of UNION and ORDER BY

[smantscheff]

Put parentheses around the subqueries. And google for "incorrect usage of UNION and ORDER BY"

[muppet]

hmm, I don't understand where to put the parentheses, which sub query do you mean?

[smantscheff]

I mean each of the SELECT clauses up to the following union, like
(SELECT a FROM x...) UNION (SELECT b FROM Y order by z) UNION (SELECT...)

[muppet]

Success! Thank you for that, its been nagging at me for days.

(SELECT a FROM x...)
UNION
(SELECT b FROM Y order by z
order by b desc limit 5)
UNION
(SELECT...)

[smantscheff]

Does the LIMIT clause work in this condition? I never tried it, but I know that you cannot use LIMIT in a subquery like in SELECT a FROM (SELECT b FROM c LIMIT x).

[muppet]

Yes, looks like you can order the sub query before the union.

As long as there are parentheses around the subs ;)