• Welcome, Guest. Please login.
 

SQL/SQLite question: special order on a select statement across two columns

Started by Bern Ertl, December 09, 2012, 03:22:43 pm

Previous topic - Next topic

Bern Ertl

I'm not sure if this is possible or not, but thought I'd ask the SQL/SQLite gurus here...

I've got a table that includes two columns A and B filled with integer values like so (in ascending order on Column A):










































Column AColumn B
12
225
34
426
56
......
253
265
......


I'd like to retrieve records from the table sorted by the minimum value across both columns A and B with column B taking precedence.  Like so:






































Column AColumn B
12
225
253
34
426
265
56
......


It doesn't look like the ORDER BY clause in a SELECT statement gives me any means to achieve this.  Or does it?  Any other elegant solution available?

Fred Meier


Bern Ertl

I tested just now.  Unfortunately, that concatenates the values in the two columns as strings and then does a string sort/order on the result.  It did not produce the desired results.

I may have a solution involving a new column and a couple of triggers, but I haven't tested it yet, so I won't post it here for now.  If it works, I will explain what I did.

Bern Ertl

"SELECT * FROM table ORDER BY MIN( ColumnA + 0.5, ColumnB)" works. 

My new column and trigger solution also works and is essentially the same thing as the above, but retains the MIN( ColumnA + 0.5, ColumnB) value for each record in the new column so the select statement doesn't have to do the processing.