SQL WITH TIES

http://msdn.microsoft.com/zh-cn/library/ms189463(v=sql.110).aspx

指定查询结果集包含与返回的最后一行中一个或多个 ORDER BY 列中的值匹配的任何其他行。 这可能导致返回的行数多于在 expression 中指定的值。 例如，如果 expression 设置为 5，而 2 个其他行与第 5 行中 ORDER BY 列的值匹配，则结果集将包含 7 行。

只能在 SELECT 语句中且只有在指定了 ORDER BY 子句之后，才能指定 TOP...WITH TIES。 返回的记录关联顺序是任意的。 ORDER BY 不影响此规则。

CREATE TABLE students(
	id int IDENTITY(1,1) NOT NULL,
	score int NULL
) ON PRIMARY
GO
INSERT INTO students (score) VALUES (100)
INSERT INTO students (score) VALUES (100)
INSERT INTO students (score) VALUES (100)
INSERT INTO students (score) VALUES (90)
INSERT INTO students (score) VALUES (90)
INSERT INTO students (score) VALUES (85)
INSERT INTO students (score) VALUES (84)
INSERT INTO students (score) VALUES (80)
INSERT INTO students (score) VALUES (80)
INSERT INTO students (score) VALUES (75)
INSERT INTO students (score) VALUES (74)
INSERT INTO students (score) VALUES (70)

SELECT TOP 8 * FROM students ORDER BY score DESC

SELECT TOP 8 WITH TIES * FROM students ORDER BY score DESC