Return matching SQL results in defined order?

Question

I have a request to return table results with a custom order on one column and a secondary Alpha/Numeric sort (Sort on Alpha first then Numeric).

Example table:

Names                    Codes
21Tail                   F
Aardvark                 A
Lead Sled                D
Meatbox                  D
23AceTail                C
Cee One-Oh-Boom          C
Corsair                  X
Texan                    X
Widow-Maker              X
Convertor                A

output:
    Names(alpha/numeric)       Codes(Custom Sort)
    Aardvark                   A
    Convertor                  A
    Cee One-Oh-Boom            C
    23AceTail                  C
    Corsair                    X
    Texan                      X
    Widow-Maker                X
    Lead Sled                  D
    Meatbox                    D
    21Tail                     F

Codes order should be ordered by A,C,X,D,F

Update: Accepted answer and, Here AND here helped me find a resolution.

This was what worked for me:

select *
from Table
ORDER BY CASE WHEN Codes = 'A' Then 0
              WHEN Codes = 'C' Then 1
              WHEN Codes = 'X' Then 2
              WHEN Codes = 'D' Then 3
              WHEN Codes = 'F' Then 4
              Else 5 End,
              CASE WHEN NAMES LIKE '[0-9]%' Then 1 Else 0 END,
              Names

This answer may help you: [Jomoos answer](https://stackoverflow.com/a/8557307/5893316) — Martin Backasch, Aug 02 '17 at 14:01
Can you explain in words how you sorted those results? I'm guessing you want to group on Custom Sort, sort within those groups on the name column and do an overall sort by the first name in each group? — juharr, Aug 02 '17 at 14:01
@MartinBackasch This is more complicated then trying to sort numeric values that are actually strings. — juharr, Aug 02 '17 at 14:04
@juharr I am having trouble trying to even explain it other than how I already have, but it's not grouping.. I just need the output of the custom column to be in the order shown: A,C,X,D,F and then a secondary sort on the Names column by alpha/numeric. I am struggling with outputting in the A,C,X,D,F order. — eaglei22, Aug 02 '17 at 14:07
@eaglei22 So you always want A then C then X the D then F, no matter what the corresponding name values are? It really begs the question of why you don't have values that would naturally sort in the order you want for the custom sort column. Maybe if you just explained why X isn't at the end then it would be more clear. — juharr, Aug 02 '17 at 14:08
right the table rows should be sorted on, "Custom Column" in this order: A, C, X, D, and then F, but for that Letter, then sort its corresponding name by alpha/numeric. let me update my question with an example of original table before sort. — eaglei22, Aug 02 '17 at 14:12
@eaglei22 Seeing the original order doesn't really help. Knowing that you want the Custom Sort column ordered by A, C, X, D, F was the missing information. You should add that to the question. — juharr, Aug 02 '17 at 14:17

TheDetective · Accepted Answer · 2017-08-02T14:36:13.913

3

Using what I found here and here.

select *
from yourTable
order by case when Codes = "A" then 1
              when Codes = "C" then 2
              when Codes = "X" then 3
              when Codes = "D" then 4
              when Codes = "F" then 5
              else 6
         end asc,
         IF(Names RLIKE '^[a-z]', 1, 2), 
         Names

edited Aug 02 '17 at 14:36

answered Aug 02 '17 at 14:03

TheDetective

642
1
6
17

1

This doesn't produce the specified output.. It will sort "C" rows as: 23AceTail, Corsair. The example asks for: Corsair, 23AceTail – Caius Jard Aug 02 '17 at 14:23
@CaiusJard What's the issue with it? – TheDetective Aug 02 '17 at 14:24
'23AceTail' is sorted after 'Cee One-Oh-Boom' in the example output, but not in your output. I think that's an error on the OP's example, because in the comments they ask for "that Letter, then sort its corresponding name by alpha/numeric" – Jacob Krall Aug 02 '17 at 14:25
@JacobKrall I was concerned about that. I'm testing a solution now. – TheDetective Aug 02 '17 at 14:26
It's something ugly like: `ORDER BY CASE WHEN custom_sort = 'C' AND name = 'Cee One-Oh-Boom' THEN .. WHEN custom_sort = 'C' AND name = '23AceTail' THEN` ... – Caius Jard Aug 02 '17 at 14:29
Sorry, I've been using "Corsair" all this time, I should have been saying "Cee One-Oh-Boom" – Caius Jard Aug 02 '17 at 14:31
Got it, I think – TheDetective Aug 02 '17 at 14:36
Another ridiculous solution would be `ORDER BY Names COLLATE SQL_EBCDIC037_CP1_CS_AS` ;) – Jacob Krall Aug 02 '17 at 14:40
@JacobKrall I think we're going for user readable solutions. Otherwise they'll ask another question what that means, and then you can answer it ;) – TheDetective Aug 02 '17 at 14:43
In the grand scheme of things "EBCDIC codepage 37, using Windows codepage 1, case sensitive, accent sensitive" isn't so hard to read, I think. – Jacob Krall Aug 02 '17 at 14:47
I was able to figure that out, after Googling it. – TheDetective Aug 02 '17 at 14:48
Thank you all for the help. This seems to be the workable solution. I also found this reference too: https://sqlwithmanoj.com/tag/order-by-case/ – eaglei22 Aug 02 '17 at 14:53

Caius Jard · Answer 2 · 2017-08-02T14:18:05.553

0

It's not clear whether your result posted in your question is already sorted as you want it, or not. You talked about sort orders in your text, but the example doesnt align with the discussed orders.

If your example data IS sorted, then there's a bit of a problem that (in my mind at least) custom sort is a) not in order and b) the Corsair and 23AceTail rows aren't sorted int he same direction as e.g. the Lead Sled and Meatball rows.

Assuming that the example IS sorted already, Custom Sort doesnt really do anything or help you by its presence, as it's less specific (not as fine grained) as the product name, so there isn't much point sorting on it first if you use the following method:

SELECT * FROM table ORDER BY
  CASE `alpha/numeric`
    WHEN 'Aardvark' THEN 0
    WHEN 'Convertor' THEN 1
    WHEN 'Cee One-Oh-Boom' THEN 2
    WHEN '23AceTail' THEN 3
    WHEN 'Corsair' THEN 4
    WHEN 'Texan' THEN 5
    WHEN 'Widow-Maker' THEN 6
    WHEN 'Lead Sled' THEN 7
    WHEN 'Meatbox' THEN 8
    WHEN '21Tail' THEN 9
  END

If I've made wrong assumptions, let me know and I'll revise

edited Aug 02 '17 at 14:18

answered Aug 02 '17 at 14:12

Caius Jard

72,509
5
49
80

don't you want `CASE "Custom Sort"`, not `alpha/numeric`? – Jacob Krall Aug 02 '17 at 14:14
I believe the OP wants to sort on the custom sort column first with the desired order being A, C, X, D, F, then on the alpha/numeric column. – juharr Aug 02 '17 at 14:15
2

@juharr possibly, but as noted look at the order of rows in custom sort C, vs X.. C is sorted with alpha/numeric descending, vs X is alpha/numeric ascending. That gets messy – Caius Jard Aug 02 '17 at 14:21
@JacobKrall no, because that wouldn't meet the spec. Though the OP says he wants to sort on custom sort in a particular order, then names, what he says he wants cannot achieve the example posted.. So we might as well just sort the names into the order he says he wants, bypassing custom sort – Caius Jard Aug 02 '17 at 14:27
I agree: we need confirmation from @eaglei22 that they actually want "descending alphanumeric sort except for 23AceTail, which sorts after Cee One-Oh-Boom". – Jacob Krall Aug 02 '17 at 14:30
And, if that's the solution OP wants, they should just create a `sortOrder` column on their table, and sort by that.... – Jacob Krall Aug 02 '17 at 14:31
@JacobKrall +1, agree! Or better still; sorting is the front end's problem (presentation) rather than the backend's problem :) It'd be typical bad luck that we'll do all this and then the data structure he loads it into in C# will mess it all up – Caius Jard Aug 02 '17 at 14:33
Yes the requirement is to be alphanumeric sorted in descending on names column. Sorry for not specifying that. – eaglei22 Aug 02 '17 at 14:41
@eaglei22 it's not getting any better! `Corsair,Texan, Widow-Maker` in section "X" is definitely not alphameric descending.. :) – Caius Jard Aug 02 '17 at 14:53

Return matching SQL results in defined order?

2 Answers2