0

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
Improve this question
3
  • 1
    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. 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? 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. Commented Apr 8, 2015 at 15:29

1 Answer 1

Reset to default
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.

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

4 Comments

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
You've missed a key point; the OP requires the columns Names to move as well as the values within those columns.
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.
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.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.