1
SELECT
    programs.name AS program,
    program_categories.name AS category
FROM programs
INNER JOIN program_categories
    ON program_category_id = program_categories.id
ORDER BY
    program_categories.name,
    programs.name

The above code should provide the following result (I think):

Module 1:   |   foo
Module 1:   |   foo
Module 2:   |   foo
Module 3:   |   foo
Module 4:   |   foo
Module 5:   |   foo
Module 6:   |   foo
Module 7:   |   foo
Module 8:   |   foo
Module 9:   |   foo
Module 10:  |   foo
Module 11:  |   foo
Module 12:  |   foo
Module 13:  |   foo

But Instead produces:

Module 10:  |   foo
Module 11:  |   foo
Module 12:  |   foo
Module 13:  |   foo
Module 1:   |   foo
Module 1:   |   foo
Module 2:   |   foo
Module 3:   |   foo
Module 4:   |   foo
Module 5:   |   foo
Module 6:   |   foo
Module 7:   |   foo
Module 8:   |   foo
Module 9:   |   foo

Would someone mind explaining what causes this phenomenon that I seem to be ignorant of? As well as how to fix/work-around it?

Helgard Ferreira
  • 25
  • 5
  • Is there a space after the single digit entries? Try doing `ORDER BY TRIM(category), program` and see if it makes a difference. Mind you, I'd expect 10 to come before 2, simply because it's a text comparison, not integer. – 404 Jun 29 '18 at 18:43
  • There are some good approaches to this issue here (https://stackoverflow.com/q/11417975/866021) and here (https://stackoverflow.com/q/12965463/866021) – Adil B Jun 29 '18 at 18:47
  • @eurotrash There is a colon after each digit in the program name (I initially omitted this) – Helgard Ferreira Jun 29 '18 at 19:05
  • It should be sorting by ASCII. I’m not surprised to see 10 at the top, but I would think it would be 1,10,11,12,13,2,3,4... – vol7ron Jun 29 '18 at 19:14

1 Answers1

2

One simple method -- if the initial strings in category are all the same -- is to first order by the length and then the value:

ORDER BY LENGTH(category), category, program
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1
    Of course if this is going to be a routine query, it’d be better to create a new integer column for module number and sort on that. – vol7ron Jun 29 '18 at 19:16