Order by two fields producing odd results

[jez]

Hi All,

I am doing a select statement with 4 joins (LEFT), to produce a list of questions stored in the database. I want to list these by their Section in order to populate a form.

For instance i have Section 1, question 1 = What is your name... etc.

I am using order by Section, Question to try and achieve this but it does not seem to produce the desired result.
I get the Sections in order, but then i get the questions ordered as 1, 10, 20, 30, 2, 3 rather than the order within the section.


I think this is fairly straightforward order requirement, so maybe there is something i have missed.

Any advice appreciated!

Here is my full query..

SELECT 
                    Qmaster.id as QuestionnaireID, 
                    Qmaster.Questionnaire,
                    Qmaster.QuestionnaireText,
                    S.id as SectionID, 
                    S.sectionNumber,
                    S.sectionName,
                    S.sectionText,
                    Q.Question,
                    Q.Number,
                    Q.id_QuestionType,
                    Q.ShowOnWeb,
                    Q.Required,
                    Q.id_AnswersGroup,
                    Q.id as QuestionID,
                    A.Answers
            FROM Questionnaire Qmaster
            JOIN Section S ON S.id_Questionnaire = Qmaster.id
            JOIN Question Q ON S.id = Q.id_Section
            LEFT JOIN QuestionType Qt ON Qt.id = Q.id_QuestionType
            LEFT JOIN AnswerGroup A ON Q.id_AnswersGroup = A.id
             WHERE  Qmaster.id = '1'  ORDER BY SectionCount, Q.Number

 

[r937]

the answer is obvious from the evidence

ordered as 1, 10, 20, 30, 2, 3 means that the so-called "Number" column is a VARCHAR

try this --

ORDER BY SectionCount
, CAST(Q.Number AS UNISIGNED)

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[jez]

You are half right.
It WAS a varchar, but it is most certainly an INT now.

I thought the same thing, so i have changed the field but it does not seem to be reflected in my query.

In phpmyadmin for this DB it shows as an INT field and having downloaded the db to my local machine and running MySql query browser, i get slightly different results, where the section number increments correectly but the question number is not ordered within the section

e.g. Section 1 , question 1
section 1, question 2
section 1, question 2 (from another section)
section 1, question 2 (from yet another section)

The query has stayed the same though.

If i had a field that was the question order overall (not counting the section info) then the order by would be fine as it would only order on 1 field.

Thanks.

 

[jez]

Actually, ignore the stuff about the local machine as that is not where it needs to work,

It is definitely an INT on the server yet i still get the results as per the first post. Could it be casting some data or something?

 

[r937]

there is absolutely. no. way.

an INT column would ~not~ have sorted values of 1, 10, 20, 30, 2, 3 ...

no way at all

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[jez]

Hmm.. it seems i have a problem elsewhere then.

... This is a migration from a previous system and i have looked at the data in the old system, the data that was migrated and it is in fact an error in the numbering in the data,

SO.. you are exactly right and i should not have trusted the data so easily...

Thanks for the reinforcement that pushed to question other things!


Jez