CODEWITHSUNDEEP
mysqlsqlsql-order-by
Simian
Simian

Reputation: 864

SQL SELECT ORDER BY multiple columns depending on value of other column

I have a table with the following columns:

id | revisit (bool) | FL (decimal) | FR (decimal) | RL (decimal) | RR (decimal) | date

I need to write a SELECT statement that will ORDER BY on multiple columns, depending on the value of the 'revisit' field.

  1. ORDER BY 'revisit' DESC - records with this field having the value 1 will be first, and 0 will be after
  2. If 'revisit' = 1 order by the lowest value that exists in FL, FR, RL and RR. So if record 1 has values 4.6, 4.6, 3.0, 5.0 in these fields, and record 2 has values 4.0, 3.1, 3.9, and 2.8 then record 2 will be returned first as it holds a lowest value within these four columns.
  3. If 'revisit' = 0 then order by date - oldest date will be first.

So far I have the 'revisit' alone ordering correctly, and ordering by date if 'revisit' = 0, but ordering by the four columns simultaneously when 'revisit' = 1 does not.

SELECT *
FROM vehicle
ORDER BY
`revisit` DESC,
CASE WHEN `revisit` = 1 THEN `FL` + `FR` + `RR` + `RL` END ASC,
CASE WHEN `revisit` = 0 THEN `date` END ASC

Instead it seems to be ordering by the total of the four columns (which would make sense given addition symbols), so how do I ORDER BY these columns simultaneously, as individual columns, rather than a sum.

I hope this makes sense and thanks!

Upvotes: 2

Views: 2323

Answers (1)

GolezTrol
GolezTrol

Reputation: 116100

In your current query, you order by the sum of the four columns. You can use least to get the lowest value, so your order by clause could look like:

SELECT *
FROM vehicle
ORDER BY
  `revisit` DESC,
  CASE WHEN `revisit` = 1 THEN LEAST(`FL`, `FR`, `RR`, `RL`) END ASC,
  CASE WHEN `revisit` = 0 THEN `date` END ASC

Of course this would sort only by the lowest value. If two rows would both share the same lowest value, there is no sorting on the second-lowest value. To do that is quite a bit harder, and I didn't really get from your question whether you need that.

Upvotes: 4

Related Questions