SQL Server 2008: Unexpected results using SELECT TOP

Question

Can anyone explain the SQL Server 2008 behaviour I'm seeing?

Given a simple table definition:

Column          Type          Nullability
id (PK)         int           not null
author_id       int           null
title           varchar(255)  not null
body            varchar(MAX)  not null
type            varchar(255)  null
comments_count  int           null

"SELECT * FROM posts ORDER BY comments_count DESC" returns:

id  author_id  title                               comments_count
--- ---------- ----------------------------------- --------------
1   1          Welcome to the weblog               2             
2   1          So I was thinking                   1             
3   0          I don't have any comments           0             
4   1          sti comments                        0             
5   1          sti me                              0             
6   1          habtm sti test                      0             
7   2          eager loading with OR'd conditions  0

but "SELECT TOP 3 * FROM posts ORDER BY comments_count DESC" returns:

id  author_id  title                               comments_count
--- ---------- ----------------------------------- --------------
1   1          Welcome to the weblog               2             
2   1          So I was thinking                   1             
4   1          sti comments                        0

instead of returnsing row IDs 1, 2 and 3 as I would expect.

Thanks Nick

Answer 1

The rows with id 3 and 4 have a tie when ordered by the comments_count column. Standard SQL says that it's up to the vendor implementation to determine sort order if there's a tie or if you don't specify any ORDER BY clause.

I suggest you specify the order if you want a certain order:

SELECT TOP 3 * FROM posts ORDER BY comments_count DESC, id ASC