I'm working with Microsoft SQL Server 2008 (SP3) - 10.0.5520.0 (X64) Standard Edition (64-bit) on Windows NT 6.0 (Build 6002: Service Pack 2)
Description:
[ORC_RESULT]andGROUPING_SELECTareCTEtable.
I have strange bug with CTE (common table expression).
SELECT a.keysbor
--,gs.[rank]
FROM [ORC_RESULT] AS a inner JOIN GROUPING_SELECT AS gs
ON a.keysbor = gs.keysbor
WHERE gs.[RANK] = 1
ORDER BY a.keysbor
result 36 rows (no duplicate rows)
SELECT a.keysbor
,gs.[rank]
FROM [ORC_RESULT] AS a inner JOIN GROUPING_SELECT AS gs
ON a.keysbor = gs.keysbor
WHERE gs.[RANK] = 1
ORDER BY a.keysbor
result 29 rows
Q: Why filter gs.[RANK] = 1 have applied only when field gs.[rank] exists in SELECT statement?
without gs.[rank] with gs.[rank] rank
10072002992443 10072002992443 1
10072002992444 10072002992444 1
10072002992445 10072002992445 1
10072002992446 10072002992446 1
10072002992447 10072002992447 1
10072002992448 10072002992448 1
10072002992449 10072002992449 1
10072002992450 10072002992450 1
10072002992451 10072002992451 1
10072002992452 10072002992452 1
10072002992453 10072002992453 1
10072002992454 10072002992454 1
10072002992455 10072002992455 1
10072002992456 10072002992456 1
10072002992457 10072002992457 1
10072002992458 10072002992458 1
10072002992459 10072002992459 1
10072002992460 10072002992460 1
10072002992461 2
10072002992462 2
10072002992463 2
10072002992464 2
10072002992465 2
10072002992466 2
10072002992467 2
10072002992736 10072002992736 1
10072002992866 10072002992866 1
10072002992867 10072002992867 1
10072002992868 10072002992868 1
10072002992869 10072002992869 1
10072002992870 10072002992870 1
10072002992871 10072002992871 1
10072002992872 10072002992872 1
10072002992873 10072002992873 1
10072002992874 10072002992874 1
10072002992875 10072002992875 1