July 22, 2013

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.

Author: Nabin Nepal (Starx)

Hello, I am Nabin Nepal and you can call me Starx. This is my blog where write about my life and my involvements. I am a Software Developer, A Cyclist and a Realist. I hope you will find my blog interesting. Follow me on Google+

...

Please fill the form - I will response as fast as I can!