Query of queries doesnt preserve order

dev
1

I just updated from 5.3 something to 5.4.3.2 and noticed when union’ing results to return from two queries, the order is not preserved. So far this is when the second query is empty, haven’t tested what happens if it has records.

                <cffile 
			action = "append"
			file = "/web-inf/lucee/logs/a.log"
			output = "#SerializeJSON(a)#"
		>
		<cfquery dbtype = "query" name = "a">
			SELECT * FROM a
			UNION
			SELECT * FROM
			b
		</cfquery>
		<cffile 
			action = "append"
			file = "/web-inf/lucee/logs/a.log"
			output = "#SerializeJSON(a)#"
		>

Is there a better way to just concat two query results?

2

You should be able to do something like this:

<cfquery dbtype = "query" name = "a">
	SELECT *, 1 as o
	FROM a
	UNION
	SELECT *, 2 as o
	FROM b
	ORDER BY o
</cfquery>

(Untested).

Or if you wanted to be a bit silly:

both = duplicate(a).addRow(b.reduce((rows, row)=>rows.append(row), []))

:wink:

1 Like