Reordering issue 1

[Sleidia]

Hi guys

Could someone tell me what query to use to transform this

+----------+-----------------+----------------------------------+---------------
| table_ID | service_type_ID | service_type_name                | service_order
+----------+-----------------+----------------------------------+---------------
|        1 |            NULL | service 1                        | 1
|        2 |            NULL | service 2                        | 2
|        4 |               1 | NULL                             | 1
|        5 |               1 | NULL                             | 2
|        6 |               1 | NULL                             | 3
|        7 |               2 | NULL                             | 1
|        8 |               2 | NULL                             | 2
+----------+-----------------+----------------------------------+---------------

into this?

+----------+-----------------+----------------------------------+---------------
| table_ID | service_type_ID | service_type_name                | service_order
+----------+-----------------+----------------------------------+---------------
|        1 |            NULL | service 1                        | 1
|        4 |               1 | NULL                             | 1
|        5 |               1 | NULL                             | 2
|        6 |               1 | NULL                             | 3
|        2 |            NULL | service 2                        | 2
|        7 |               2 | NULL                             | 1
|        8 |               2 | NULL                             | 2
+----------+-----------------+----------------------------------+---------------

Thanks!

 

[towerbase]

You haven't actually specified the sort order but assuming that you want the rows ordered into service_type_name, service_order order where a NULL service_type_name is equivalent to 'service 0' then I suggest that you consider using the CASE function.

Something along these lines should work

SELECT *, CASE service_type_name 
          WHEN NULL THEN 'service 0'
          ELSE service_type_name
          END AS sort_key
FROM table
ORDER BY sort_key, service_order

I haven't tested this out so watch out for typos and so on.
The CASE function can be very useful in situations like this.

Andrew
Hampshire, UK

 

[Sleidia]

Thanks Andrew

The query doesn't work (mysql 5.0 here) and I don't get what 'service 0' is for.

As for the order needed, each service_type should be ordered by service_order and under every type, we should have services ordered by service_order as well.

Thanks again

 

[towerbase]

It was before 6am when I wrote that post and although I couldn't sleep I was obviously not awake either.

CASE was introduced in MySQL 3.23.3 so it should work in MySQL 5.0

I think something like this should work:

SELECT *
FROM table
ORDER BY CASE service_type_name
         WHEN NULL THEN CONCAT('service ',service_type_ID)
         ELSE service_type_name
         END

I'm assuming that a service_type_id of n is the equivalent to the service_type_name string of 'service n'.


Andrew
Hampshire, UK

 

[Sleidia]

Nope, service_type_name can be anything

I really have to study how CASE works.

 

[Sleidia]

I've found the solution finally

SELECT table_ID, service_type_ID, service_order, 
        CASE 
        WHEN service_type_ID IS NULL THEN CONCAT(table_ID, 0)
	ELSE CONCAT(service_type_ID, service_order)
        END AS final_order
FROM mod_hotel_services
ORDER BY final_order