CODEWITHSUNDEEP
mysqlsql
Hashey100
Hashey100

Reputation: 984

MySQL query based on column value

I'm trying to order a row based on the values which are in the different columns, I honestly have no idea how to explain this correctly so I'll show an example.

I'm trying to create a query which orders the Failures based on the value for example SELECT * from table WHERE id='1' order by Failure1 asc,Failure2 asc,Failure3 asc

I want the query to show this as a result.

ID    Failure2 Failure3 Failure1
1     5636       123     22

Example table

ID    Failure1 Failure2 Failure3
1     22       5636     123
2     33       148      22
3     1        101      11
4     33       959      55

Upvotes: 0

Views: 242

Answers (1)

user4650451
user4650451

Reputation:

This is a little tough because what you're doing is dynamically re-ordering your columns. The only way I can think of to do this would be to have each column as a CASE statement. It's not going to be pretty or efficient though:

SELECT
  CASE
     WHEN (Failure1 > Failure2 AND Failure1 > Failure3) THEN Failure1
     WHEN (Failure2 > Failure1 AND Failure2 > Failure3) THEN Failure2
     WHEN (Failure3 > Failure1 AND Failure3 > Failure2) THEN Failure3
  END,
  --repeat Case for second and third column
FROM
   Failures
WHERE
   ID = 1

Also, double check that you can do the AND logic in the WHEN(). 90% sure you can but, worst case, you need to expand your WHENs.

Also, @MatBailie is right; this is usually done on the presentation layer, not through SQL Queries. SQL isn't really the best at this kind of logic, whereas the presentation layer is built for it.

Upvotes: 1

Related Questions