CODEWITHSUNDEEP
sqlsql-server
DevD
DevD

Reputation: 165

SQL: SELECT TOP with ORDER BY clause does not return correct result

I have a SELECT TOP query with an ORDER BY clause that does not return the correct result. Below is a sample query and its output.

Any suggestion / workaround / solution ?

Query:

CREATE TABLE #TestTop
(
    TopID INT,
    TopStr VARCHAR(MAX)
)

INSERT INTO #TestTop VALUES(1749, '');
INSERT INTO #TestTop VALUES(1750, '');
INSERT INTO #TestTop VALUES(1752, 'z');
INSERT INTO #TestTop VALUES(1739, 'a');

SELECT * FROM #TestTop ORDER BY TopStr ASC

SELECT TOP 1 * FROM #TestTop ORDER BY TopStr ASC

SELECT TOP 4 * FROM #TestTop ORDER BY TopStr ASC

DROP TABLE #TestTop;

Result:

[SELECT *]

TopID       TopStr
----------- -----------
1749        
1750        
1739        a
1752        z       

[SELECT TOP 1]

TopID       TopStr
----------- --------------
1750        

[SELECT TOP 4]

TopID       TopStr
----------- --------------
1750        
1749        
1739        a
1752        z

Upvotes: 3

Views: 2479

Answers (3)

bvr
bvr

Reputation: 4826

Try this, it works in sql server

SELECT TOP 4 * FROM #TestTop 
order by ROW_NUMBER() OVER(ORDER BY (CASE WHEN TopStr = '' THEN NULL ELSE TopStr END))

OR

SET ROWCOUNT 4
SELECT * FROM #TestTop ORDER BY TopStr ASC
SET ROWCOUNT 0

Upvotes: -4

Gerardo Lima
Gerardo Lima

Reputation: 6703

The problem here is the column that's been used for sorting: the original statements use the TopStr column and, since all its values are equal, the order in which the rows are returned is unspecified. To correct your statement just use the proper column TopID, as follows:

CREATE TABLE #TestTop
(
    TopID INT,
    TopStr VARCHAR(50)
)

INSERT INTO #TestTop VALUES(1749, '');
INSERT INTO #TestTop VALUES(1750, '');
INSERT INTO #TestTop VALUES(1752, '');
INSERT INTO #TestTop VALUES(1739, '');

SELECT '' [_ALL_],* FROM #TestTop ORDER BY TopID ASC
SELECT TOP 1 '' [TOP_1], * FROM #TestTop ORDER BY TopID ASC
SELECT TOP 4 '' [TOP_4], * FROM #TestTop ORDER BY TopID ASC

DROP TABLE #TestTop;

Upvotes: 0

mc110
mc110

Reputation: 2833

You have not specified complete ordering, so the database (whichever one it is) is entitled to return rows that match on TopStr in any order it wants. Just because your first query happens to give the ordering you want is luck, and that could change based on a database upgrade / load on the system / many other factors.

You need to add the TopId into the ordering list if you want results to be ordered by that column as well as TopStr.

Upvotes: 9

Related Questions