MySQL query based on column value

Question

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

You can't dynamically re-arrange the columns. I could write a query that re-arranged the values in the columns (such that Failure1=5636, Failure2=123, Failure3=22). But even then I'd question why you want to do this? It feels like something you should be doing in your presentation layer. — MatBailie
– MatBailie, Commented Apr 8, 2015 at 15:19
Hmmm I see, I'm not sure how I can connect different Failure's to a specific ID without following this structure. - By presentation layer do you mean doing it front-end sorting it etc? — Hashey100
– Hashey100, Commented Apr 8, 2015 at 15:21
Yes, whatever front end you have should be able to take the results as they exist in your table (Column1=ID, Column2=Failure1, Column3=Failure2, etc) and then sort them as required for presentation. — MatBailie
– MatBailie, Commented Apr 8, 2015 at 15:29

user4650451 · Accepted Answer · 2015-04-08 15:21:51Z

1

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.

answered Apr 8, 2015 at 15:21

user4650451

Sign up to request clarification or add additional context in comments.

4 Comments

user4650451 Over a year ago

also, the comment --repeat Case for second and third column isn't technically correct; the logic will be different to determine your middle and lowest value. That should get you started though

MatBailie Over a year ago

You've missed a key point; the OP requires the columns Names to move as well as the values within those columns.

user4650451 Over a year ago

In which case, I agree with you Mat, it's essentially impossible. The dirty hack would be to append an identifier to the result returned by the case statement: THEN Failure1 + 'F1' THEN Failure2 + 'F2' Now, this obviously adds a whole extra layer of problems because you need to pair out the identifier, maybe cast Failure1/Failure2 to varchar() before appending, etc... but it's the ONLY way I can think of to even remotely accomplish what the OP wants.

Hashey100 Over a year ago

Damn I did not think it would be this complex tbh, thanks for the help guys I'll take your suggestion on board and order it in the presentation layer.

Collectives™ on Stack Overflow

MySQL query based on column value

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related