select Top 3 unexpexted result?

问题:

This is table structure !

name varchar(10),

score float

With values:

('Alex',7),('john',5.6),('Tom',8.9),('Anonio',6),('sharti',7),('mamuzi',9)

I need o/p

name    score
mamuzi  9
Tom 8.9
Alex    7
sharti  7

When i try using TOP as : select top 3 * from table order by score desc I can't get the expected results

回答1:

Try using with Ties in sql server since Alex and sharti have same score

select top 3 with ties * from #t order by score desc

See here

回答2:

Unless you include an ORDER BY statement, the ordering will be random when you use TOP.

Do this:

SELECT TOP 3 WITH TIES * FROM YourTable ORDER BY score DESC

Sample SQL Fiddle.

From the documentation:

When TOP is used in conjunction with the ORDER BY clause, the result set is limited to the first N number of ordered rows; otherwise, it returns the first N number of rows in an undefined order.

Edit: just noticed the change in your sample data. To make it work now you need to add the WITH TIES clause to TOP, as pointed out in the other answers. I've updated my answer accordingly.

回答3:

You forgot ordering by score field. Modify your query as:

SELECT TOP 3 * FROM table ORDER BY score DESC

---

Answer for the last edit to your question: Using TOP and ORDER BY DESC worked for me but i tested with the new values you added to your question. My test was as follows:

DECLARE @table TABLE
(
  name varchar(10),
  score float
) 

INSERT INTO @table VALUES ('Alex', 7)
INSERT INTO @table VALUES ('john',5.6)
INSERT INTO @table VALUES ('Tom',8.9)
INSERT INTO @table VALUES ('Anonio',6)
INSERT INTO @table VALUES ('sharti',7)
INSERT INTO @table VALUES ('mamuzi',9)

SELECT TOP 3 * FROM @table ORDER BY score DESC

It works since the result is as expected.

To include sharti and his score in the results since he has the same score as Alex, you will need to use WITH TIES as Vijaykumar Hadalgi suggested (More for WITH TIES).

Modify your query as: SELECT TOP(3) WITH TIES * FROM @table ORDER BY score DESC

name    score
--------------
mamuzi  9
Tom     8,9
Alex    7
sharti  7