How to setup Order By so that null rows will order based off a later constraint
I h开发者_开发技巧ave the following query:
SELECT frst.FirstActionPath, scnd.SecondActionPath, frst.FirstIterationId, scnd.SecondIterationId,
frst.FirstTestRunId, scnd.SecondTestRunId, frst.FirstOrderId, scnd.SecondOrderId
FROM @FirstRunData frst
FULL JOIN @SecondRunData scnd
ON frst.FirstIterationId = scnd.SecondIterationId
AND frst.FirstActionPath = scnd.SecondActionPath
ORDER BY scnd.SecondIterationId, scnd.SecondOrderId, frst.FirstIterationID, frst.FirstOrderId
It gives me the following results:
The top row is a row that is in the @FirstRunData table but has not matching row in the @SecondRunData row.
How can I change my order by so that the extra row will be after the row with the 10000 value in FirstOrderId?
You might be able to take advantage of COALESCE in your ORDER BY clause. For example,
ORDER BY COALESCE(scnd.SecondIterationId, frst.FirstIterationID, 9999),
COALESCE(scnd.SecondOrderId, frst.FirstOrderId, 9999),
frst.FirstIterationID, frst.FirstOrderId
Something like this my address your example, but you'll need to alter it based on your requirements for all conditions.
精彩评论