CODEWITHSUNDEEP
mysql
Chamath Gunasekara
Chamath Gunasekara

Reputation: 129

How to use Mysql ORDER BY Case to arrange the queries results

I have this query in which I included a case statement:

SELECT
    tsp_filtered_data.increment_id,
    tsp_filtered_data.ESN,
    tsp_filtered_data.Model,
    tsp_filtered_data.Fault,
    CASE 
       WHEN tsp_filtered_data.ESN RLIKE '^[0-9A-F]{8}$' 
          THEN 'valid ESN' 
          ELSE 'Invalid ESN'  
    END AS ESN_status 
FROM
    tsc_document_status
INNER JOIN 
    tsp_filtered_data ON tsp_filtered_data.Document_id = tsc_document_status.warehouse_transfer_note_id
ORDER BY 
    tsp_filtered_data.ESN DESC

and it returns this output:

enter image description here

Can I use those results of 'valid ESN' and 'Invalid ESN' in to order by

my requirement is to place all the 'Invalid ESN' lists to the top of the queried results, while next the 'Valid ESN' list.

how can i do the modification to the query..

Upvotes: 0

Views: 50

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521053

Use ORDER BY FIELD:

ORDER BY FIELD(ESN_status, 'Invalid ESN', 'Valid ESN')

Full query:

SELECT
    t2.increment_id,
    t2.ESN,
    t2.Model,
    t2.Fault,
    CASE WHEN t1.ESN RLIKE '^[0-9A-F]{8}$'
         THEN 'Valid ESN' ELSE 'Invalid ESN' END AS ESN_status 
FROM tsc_document_status t1
INNER JOIN tsp_filtered_data t2
    ON t1.Document_id = t2.warehouse_transfer_note_id
ORDER BY
    ORDER BY FIELD(ESN_status, 'Invalid ESN', 'Valid ESN')

Note that I introduced table aliases which leaves your query easier to read (IMO). You are free to add more levels of sorting to your ORDER BY clause, though my answer should address what you requested in your question.

Upvotes: 3

Harshil Doshi
Harshil Doshi

Reputation: 3592

SELECT
    tsp_filtered_data.increment_id,
    tsp_filtered_data.ESN,
    tsp_filtered_data.Model,
    tsp_filtered_data.Fault,
    case when tsp_filtered_data.ESN RLIKE '^[0-9A-F]{8}$' then 'valid ESN' else 'Invalid ESN'  end AS ESN_status 
    FROM
    tsc_document_status
    INNER JOIN tsp_filtered_data ON tsp_filtered_data.Document_id = tsc_document_status.warehouse_transfer_note_id
    ORDER BY ESN_status,tsp_filtered_data.ESN DESC      //Modified line

What it will do is that first,it will sort the whole result set in Ascending order of ESN_status. Now, for the records having same ESN_status, it will sort them in descending order of tsp_filtered_data.ESN

Hope it helps!

Upvotes: 0

Related Questions