I don't know what you mean by "technically impossible". What Bart asked for is entirely possible, and commonly done. You very well can put an arbitrary expression in an ORDER BY clause, including a CASE statement, so that it does what is requested. Failing that, you can use an inner query in the FROM clause to generate the fields you want to sort on, and then ORDER BY on them in the outer query. -- Darren Duncan
On 2015-03-31 5:31 PM, R.Smith wrote: > On 2015-04-01 01:50 AM, Bart Smissaert wrote: >> Say I have a table with 3 fields. Depending on a value in field 1 (this >> value will be either 1 or 2) >> I want to do a different sort order sorting on fields 2 and 3. >> This will be either order by field2 desc, field3 desc or field3 desc, >> field2 desc. >> I thought of a union, but doesn't allow this. >> Any suggestions? > > Well, this is technically impossible (influencing an SQL statement from the > values returned from that statement is impossible for obvious reasons). > > But, if you do this: > > SELECT Field1, Field2, Field3, Field4, (CASE Field1 WHEN 1 THEN Field2 ELSE > Field3) AS Sort1, (CASE Field1 WHEN 0 THEN Field2 ELSE Field3) AS Sort2 > FROM SomeTable > WHERE 1 > ORDER BY Sort1, Sort2; > > You should achieve the exact result. > > NOTE: This is a really really bad way of doing things, the sort order should > not > be determined like this in any sane system - those settings should live in a > DB > or at least a table outside of the data being inspected.
