MySQL sort by 3 columns or UNION them

Question

I have understandint ORDER BY problem in MySQL . I need to sort a table by three criteria

1 - first I want to sort by TYPE_OF_WORK , so all data should be in alphabetical order, for example

 dech_rap_bus dech_rap_bus ger_dem_dech_bus ger_dem_dech_bus ger_dem_stp_pp ... 

RESULT => http://sqlfiddle.com/#!2/b2a858/6

2-second I want to sort by PROJECT_VERSION , so all the data should be in alphabetical order, but in accordance with 1 criterion, for example

 dech_rap_bus V123-V1234 dech_rap_bus V300 ger_dem_dech_bus V123-V1234 ger_dem_dech_bus V300 ger_dem_stp_pp V123-V1234 

RESULT => http://sqlfiddle.com/#!2/b2a858/7

So 1 and 2 work fine.

3 - and after that I want to sort by not_existing column

RESULT => http://sqlfiddle.com/#!2/b2a858/5

and I don’t know what he’s actually doing, but I don’t see the results ... I just want

dech_rap_bus V300

where the column NOT_EXISTING 1 is at the end, and when more than NOT_EXISTING = 1 >, to sort them all, except at the end of the table.

I tried myself that UNION of 2 options will help me

 /* Selecting all data where not_existing is not 1 or NULL ---> working good! */ ( SELECT * FROM atm_mti_view WHERE project_function='FRS01' AND on_big_project_id = 12 AND (not_existing != 1 OR not_existing IS NULL) ORDER BY type_of_work ASC, project_version ASC ) UNION /* Selecting all data where not_existing is 1 ---> working good! */ ( SELECT * FROM atm_mti_view WHERE project_function='FRS01' AND on_big_project_id = 12 AND not_existing = 1 ORDER BY type_of_work ASC, project_version ASC ) 

but what this piece of code does is put the non-existent dech_rap_bus at the end, fine, but it messed up the version sorting, WHY ???

CM. RESULT HERE => http://sqlfiddle.com/#!2/b2a858/8

Why? I just want MERGE to select two results, what am I doing wrong?

Thanks.

Answer 1

If you do

 order by (case when not_existing is null then 0 else not_existing end) desc ,type_of_work ASC, project_version ASC 

This will come first.

your request is not ordered because you have a different project value for dech_rap_bus

 TYPE_OF_WORK PROJECT_VERSION dech_rap_bus V123-V1234 dech_rap_bus V300 

Answer 2

Doesn't that give you what you want?

http://sqlfiddle.com/#!2/b2a858/26

Just select not_existing first?

What is the problem? You have your own view, but with non-existing entries at the end - they are also sorted the same way.

Answer 3

You do not need UNION , you can achieve this with this query:

 SELECT * FROM atm_mti_view WHERE project_function='FRS01' AND on_big_project_id = 12 ORDER BY IF(not_existing = 1, 1, 0) ASC, type_of_work ASC, project_version ASC;