CODEWITHSUNDEEP
mysqlsql
C.O.
C.O.

Reputation: 2329

SQL order by value from two columns based on condition

I had a table with entries belonging to some catergory. Each entry has an ID (e_ID), each category has an ID (c_ID) and there is a column to define the order of the content (sort1) holding a custom order represented by numbers.

Now a second optional category has been added. If that aplies the sorting number goes into a new (sort2) column so that the order of the primary catergory does not get overwritten.

I used to use ORDER BY sort1 but would now need sort1 if the curent catergory is the entry's primary category and sort2 if it's the secondary catergory. All entries that belong to the same category shall be sorted by the user so, the data gets stored on two columns. Can I use both in this way in an order statement?

e_ID    c_ID    c2_ID   sort1   sort2
1       7       3       1       27
2       3       7       37      3
3       7       -       2       -
4       2       4       99      81

expected order 1,3,2 for categroy 7

Upvotes: 0

Views: 127

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

I think you are looking for this:

select t.*
from t
order by coalesce(c2_id, c_id), -- put similar categories together
         coalesce(sort2, sort1) -- order according to your priority

If you want to limit to a particular category, then add:

where coalesce(c2_id, c_id) = 7

Upvotes: 2

Dunno
Dunno

Reputation: 3684

If I understand what you want correctly then the following should work:

SELECT * FROM my_table
    WHERE c_ID = 7
    ORDER BY sort1
UNION
SELECT * FROM my_table
    WHERE c2_ID = 7
    ORDER BY sort2

If you're not familiar with UNION - it simply combines two selections into one

Upvotes: 1

Related Questions