MySQL - How to ORDER BY name, then selectively on table by alphanumeric?

Question

I want to order query results by name, then on a specific table alphanumerically.

This works for all results by alphanumeric:

`ORDER BY 'name'+0 ASC"`;

But I need only table 'week' sorted alphanumerically, all other results alpha. I have tried the following but not working:

ORDER BY name, week('name'+0) ASC";

Table week results should be like:

Week 1
Week 2
Week 3
Week 4
...
Week10

Not:

Week 1
Week 10...

See db-fiddle

See updated OP. Schema shouldn't be necessary as there is no problem with the query itself. — IlludiumPu36, Sep 18 '19 at 05:53
@IlludiumPu36 But sample data might be needed here. Also, tell us which version of MySQL you are using. — Tim Biegeleisen, Sep 18 '19 at 05:53
@CodeCaster Correct, but how to do that on one table, otherwise sort on alpha for all other results? — IlludiumPu36, Sep 18 '19 at 05:56

Dark Knight · Accepted Answer · 2019-09-18T07:09:15.147

2

For order by clause, you can use custom field like below. For week name field use below in ORDER BY clause.

As per our conversation, added one column as seq in select clause and then based on data we did following ORDER BY clause

1. Added seq in select clause of all unions.

2. Order by changes:

ORDER BY seq ASC, 
    CASE seq
        WHEN 1 THEN 0 
        WHEN 2 THEN CAST(SUBSTRING(name, 6) AS SIGNED)
        WHEN 3 THEN CAST(SUBSTRING(name, -2) AS SIGNED)
        WHEN 4 THEN 0
        WHEN 5 THEN CAST(SUBSTRING(name, 6) AS SIGNED)
        WHEN 6 THEN 0
    END ASC,
    name  ASC

First, each union will contain their data as selected i.e. the First query in the union have results at the top and then second and so on.
In those union, in which name is a string, will be not ordered in second order by clause, only year, week and unit_name unions are sorted based on number
Sort all remaining unions by name.

See in Fiddle

edited Sep 18 '19 at 07:09

answered Sep 18 '19 at 05:59

Dark Knight

6,116
1
15
37

thanks but where do I specify the table 'week' that needs aphanumeric sorting? – IlludiumPu36 Sep 18 '19 at 06:05
Show me your complete query just reducing select fields. – Dark Knight Sep 18 '19 at 06:07
see fiddle in op – IlludiumPu36 Sep 18 '19 at 06:08
We do have `Year 1` , `Week 1` and some string values in `name` field, how do you want to sort them? Like I got idea about all records with `Week` string but what about others? – Dark Knight Sep 18 '19 at 06:12
[Fiddle](https://www.db-fiddle.com/f/5pBwNx74Qv5u1igxxXy2rN/5) do you want this? – Dark Knight Sep 18 '19 at 06:15
OK, sort tables week, unit and year by alphanumeric and all other tables alphabetically. Your fiddle works OK on alphanumeric but other results that don't have numeric values are not sorted alphabetically – IlludiumPu36 Sep 18 '19 at 06:26
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/199601/discussion-between-jitendra-yadav-and-illudiumpu36). – Dark Knight Sep 18 '19 at 06:29

MySQL - How to ORDER BY name, then selectively on table by alphanumeric?

1 Answers1