MYSQL Order by with case
Anil D’s Question:
I have a Product Table which has following structure.
ProductID ProductName ProductType
1 Irrigation 1
2 Landscape 2
3 Sleeving 3
4 Planting 4
Now i need to returns rows in order of product type 3,2,4,1
For this i used MYSQL FIELD method which works fine like this
Select * from product order by FIELD(producttype,3,2,4,1)
This is working fine,
My problem is if the productname is empty for producttype 3, then it should take next productname which is not empty, so in such case result order should be 2,4,1,3.
So first condition is records need to be in following order of product type
Sleeving 3
Landscape 2
Planting 4
Irrigation 1
But if Productname for producttype 3 is empty then order need to be
Landscape 2
Planting 4
Irrigation 1
3
And further Productname for producttype 2 is empty then order need to be
Planting 4
Irrigation 1
3
2
From this result i just need to pick first record.
I hope i clear my point
Any help would be appreciated
This is a very complex query and will never [citation needed] work as you need, because it should identity the next value in another row which can be anything. It is possible with views, functions, pivoting and many joins but as the number of rows, I dont think it can give a result.
However, to answer directly, YES Order BY with CASE is possible
Select * from product order by
CASE WHEN productname IS NULL then FIELD(producttype, 2, 4, 1, 3)
CASE WHEN productname IS NOT NULL THEN FIELD(producttype, 3, 2, 4, 1)
Again, This is not full query and is not the solution
If your rows are limited you can chain up your queries like this
Select * from product order by
CASE WHEN productname IS NULL then FIELD(producttype, 2, 4, 1, 3)
CASE WHEN (SELECT productname n FROM product WHERE producttype = '2') IS NULL THEN FIELD (4, 1, 3, 2)
As you see I have fixed the query to search for the row with ‘2’ as product type in it. You can know them by making another query and pivoting it the main query.
Again it is not neccessary, process the rows server side it will very very easier than this.
