SQL Server 2008: Unexpected Results Using SELECT TOP
Can anyone explain the behavior of SQL Server 2008 that I am 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 returning row IDs 1, 2 and 3 as I expected.
Thanks Nick
+2
please delete me
source
to share
1 answer
Rows with id 3 and 4 are anchored if they are ordered by column comments_count
. Standard SQL says to define the sort order before implementing the provider if there is a relationship or if you do not specify any clause ORDER BY
.
I suggest you specify the order if you want a specific order:
SELECT TOP 3 * FROM posts ORDER BY comments_count DESC, id ASC
+15
source to share