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

i have select top query order clause not return correct result. below sample query , output.

any suggestion / workaround / solution ?

query:

create table #testtop (     topid int,     topstr varchar(max) )  insert #testtop values(1749, ''); insert #testtop values(1750, ''); insert #testtop values(1752, 'z'); insert #testtop values(1739, 'a');  select * #testtop order topstr asc  select top 1 * #testtop order topstr asc  select top 4 * #testtop order topstr asc  drop table #testtop; 

result:

 [select *]  topid       topstr ----------- ----------- 1749         1750         1739        1752        z         [select top 1]  topid       topstr ----------- -------------- 1750          [select top 4]  topid       topstr ----------- -------------- 1750         1749         1739        1752        z          

try this, works in sql server

select top 4 * #testtop  order row_number() over(order (case when topstr = '' null else topstr end)) 

or

set rowcount 4 select * #testtop order topstr asc set rowcount 0