How Top 1 Works in SQL Server 2012

Question

I have a table with the following data

  IF OBJECT_ID('TEMPDB.DBO.#t1', 'U') IS NOT NULL
            DROP TABLE #t1;
CREATE TABLE #t1
    ([c1] varchar(100), [c2] varchar(10), [c3] varchar(100), [c4] varchar(100))
;

INSERT INTO #t1
    ([c1], [c2], [c3], [c4])
VALUES
    (93, '60-1.1.1.', 60, 3),
    (104, '60-1.2.1.', 60, 3),
    (102, '60-1.1.2.', 60, 3),
    (101, '60-1.2.2.', 60, 3),
    (92, '60-1.1.3.', 60, 3),
    (96, '60-1.2.3.', 60, 3),
    (103, '60-1.1.4.', 60, 3),
    (94, '60-1.2.4.', 60, 3),
    (105, '60-1.2.5.', 60, 3),
    (97, '60-1.2.6.', 60, 3),
    (99, '60-1.2.7.', 60, 3),
    (100, '60-1.2.8.', 60, 3),
    (98, '60-1.2.9.', 60, 3),
    (95, '60-1.2.10.', 60, 3),
    (91, '60-1.2.11.', 60, 3)
;
select * from #t1

the result of the table is as follows

select * from #t1 order by c3,c4

Now I have completed the following query: I got the result as expected

select  Cast(c4 AS VARCHAR(2)) + '~'+ Cast(c1 AS VARCHAR(100)) AS c5,* from #t1

The result of the above query was as follows:

Now I used top 1 to write the record, I wrote the code as follows

select top 1 Cast(c4 AS VARCHAR(2)) + '~'+ Cast(c1 AS VARCHAR(100)) AS c5,* from #t1

The result of the above query was as follows:

Now I used top with the order by clause, after which I got the following result

select top 1 Cast(c4 AS VARCHAR(2)) + '~'+ Cast(c1 AS VARCHAR(100)) AS c5,*
 from #t1 order by c3,c4

Question: why there have been changes in the results of the last 2 queries, since I would like to expect the same result?

let me ask you as follows:

When I execute the first query without order, I got 93 record values, so when I execute the top 1 with column order, I expect the same result. in the request, according to my assumption, there was no order effect on caluse

1

100 ,

2

• 1

. ,

• 2

( 1) . 1 , .

.

Answer 1

ORDER BY, Sql Server , . , , . ORDER BY, , Sql Server , .

, , Sql Server . , , , . , "" - .

, . , . , - , , , . - , Sql Server ( ) -, .

, TOP, ORDER BY, , , .

---

ORDER BY ORDER BY, c3 c4. . , Sql Server - , .

, Sql Server , . ORDER BY Sql Server, , , , , , .

, , , ORDER BY, , . c1, c1 ORDER BY.

Answer 2

ORDER BY, .
, .

Answer 3

Without proper order in order, you will continue to get unexpected results due to the fact that tables are not necessarily read in any order. The order by command you quoted in your last query emphasizes this problem. Since all data has the same values ​​c3, c4, they all have equal priority when returning. This means that the order of the returned values ​​is not specified. I recommend checking out this post to find out more.