SQL Order By within Order By

So I have two tables, Questions and Answers, joined by a many to many relation table, QuestionsAnswers. Questions have a Sort column that allows me to control how they are displayed to the user, and QuestionsAnswers also has a sort column that allows me to control the order of the answers for each Question. My problem is that I am trying to write a SQL query that will select all the Questions and their Answers ordered first by Question.Sort and then by QuestionsAnswers.Sort. Both sort columns are nullable and integers in these columns should take precedence over nulls.

I can get the Order By Questions.Sort to work fine but once I add in the second sort column, it goes awry. For instance, if a Question has a Sort = 0 meaning it should be the first Question displayed but does not have any preference for its Answers, it will get ordered below a QuestionsAnswers row that has Questions.Sort = null and QuestionsAnswers.Sort = 0.

Any tips or thoughts if this is even workable would be great.

edit:

SELECT
    Q.Id AS QuestionId,
    Q.Name AS Question,
    A.Id AS AnswerId,
    A.Text AS Answer
FROM
    dbo.Questions AS Q
INNER JOIN
    dbo.QuestionsAnswers AS QA
        ON Q.Id = QA.QuestionId
INNER JOIN
    dbo.Answers AS A
        ON QA.AnswerId = A.Id
ORDER 开发者_运维百科BY
    ISNUMERIC(Q.Sort) DESC,
    Q.Sort,
    Q.Id,
    A.Text;

ORDER BY COALESCE(Questions.Sort, 999999999), COALESCE(Answers.Sort, 999999999)

From what I can see something like this should do the trick. Unless I am misssing something.

select questions.*, answers.* 
from questionsanswers qa
inner join questions
    on questions.idquestions = qa.questionid
inner join answers
    on answers.idanswers = qa.answerid
order by isnull(questions.sort), questions.sort, isnull(qa.sort), qa.sort