What do you guess the result of this Query:
SELECT TOP 2 * FROM (SELECT TOP 3 * FROM [SqlBug]) AS T
ORDER BY col1 DESC ;
and this is the scripts for table [SqlBug]:
CREATE TABLE [dbo].[SqlBug](
[col1] [int] NULL,
[col2] [nchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[col3] [int] NULL
) ON [PRIMARY]
the test data in the table is :
col1 col2 col3
1 A 11
2 A 12
3 B 12
4 C 13
5 D 14
6 E 15
My expect result is :
3 B 12
2 A 12
but the real result is :
6 E 15
5 D 14
The "ORDER BY col1 DESC" sentence should act on the outer select but not the inner select. I think it 's most reason is the PRI for "ORDER BY" and parenthesis when used with "Top", so is this a bug for sql server 2005?