Keeping the same order in Subquery with Row Number() Over (Order By)

Question

Post reply

Keeping the same order in Subquery with Row Number() Over (Order By)

dja984

SSC Rookie

Points: 42
More actions
January 6, 2015 at 10:12 am

#299970

Hello,
Trying to keep order of the inner (sub) query the same for an outbound service call, while only targeting 1 column at a time (of several).. How can I get it to always order correctly so all of the rows line up?
2 samples below, note how the target is in bold.. I want the sub to always be in the SAME order, but can only have 1 column result, with the row number being targeted in it's own outer query, used as the ordering key.
right now the ordering is only working properly when the rownumber is also targeted (2 column result)
Any takers?
thanks in advance...
SELECT
Date
FROM (SELECT ROW_NUMBER() OVER (ORDER BY Date ASC, ProcessOrder ASC, (
CASE
WHEN TN= 'X'
THEN 0
WHEN TN= 'Y'
THEN 1
WHEN TN= 'Z'
THEN 2
END)) AS RowNumber, Date, TN, Account, Amount, ProcessOrder FROM (
SELECT
FROM ..
JOIN ..
ON ..
WHERE
AND etc...
GROUP BY a, b, c, d, e
UNION SELECT ..
FROM ..
WHERE ...
AND ...
AND ) T1) T2
SELECT
Amount
FROM (SELECT ROW_NUMBER() OVER (ORDER BY Date ASC, ProcessOrder ASC, (
CASE
WHEN TN= 'X'
THEN 0
WHEN TN= 'Y'
THEN 1
WHEN TN= 'Z'
THEN 2
END)) AS RowNumber, Date, TN, Account, Amount, ProcessOrder FROM (
SELECT
FROM ..
JOIN ..
ON ..
WHERE
AND etc...
GROUP BY a, b, c, d, e
UNION SELECT ..
FROM ..
WHERE ...
AND ...
AND ) T1) T2
This will be the ordering key:
SELECT
RowNumber
FROM (SELECT ROW_NUMBER() OVER (ORDER BY Date ASC, ProcessOrder ASC, (
CASE
WHEN TN= 'X'
THEN 0
WHEN TN= 'Y'
THEN 1
WHEN TN= 'Z'
THEN 2
END)) AS RowNumber, Date, TN, Account, Amount, ProcessOrder FROM (
SELECT
FROM ..
JOIN ..
ON ..
WHERE
AND etc...
GROUP BY a, b, c, d, e
UNION SELECT ..
FROM ..
WHERE ...
AND ...
AND ) T1) T2
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

...

ORDER BY RowNumber;

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

dja984 SSC Rookie Points: 42 More actions · Answer 2

mister.magoo (1/6/2015)
...
ORDER BY RowNumber;

Thank you! Seems the problem was a combination of this, and something else with the middleware.

Much appreciated

Keeping the same order in Subquery with Row Number() Over (Order By)

Cookies on SQLServerCentral