The third day of SQL Learning -- SQL about with ties

About with ties

If with ties is generally used in combination with Top and order by, the extra return values of the last data record will be queried (explanation: if the order by parameter is used to sort TOP n (PERCENT) the previous n (pencent) records are returned, but n + 1... If the parameter values of n + k records are the same as those of the n records after order by, then n + 1 ,... And n + k are also returned. N + 1 ,... , N + k is the additional return value ).

Lab:

Experiment Table (PeopleInfo ):
Copy codeThe Code is as follows:
Create table [dbo]. [PeopleInfo] (
[Id] [int] IDENTITY (1, 1) not null,
[Name] [nchar] (10) COLLATE Chinese_PRC_CI_AS NULL,
[Numb] [nchar] (10) COLLATE Chinese_PRC_CI_AS not null,
[Phone] [nchar] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]

Insert data into the table:
Copy codeThe Code is as follows:
Insert into peopleinfo ([name], numb, phone) values ('Li Huan ', '123', '123 ')
Insert into peopleinfo ([name], numb, phone) values ('Li Huan ', '123', '1 ')
Insert into peopleinfo ([name], numb, phone) values ('Lee name', '123', '123 ')
Insert into peopleinfo ([name], numb, phone) values ('Lee name', '123', '123 ')

View All inserted data:
Copy codeThe Code is as follows:
Select * from dbo. PeopleInfo

Result chart:

Step 1: Do not use with ties

Code:
Copy codeThe Code is as follows:
Select top 3 * from peopleinfo order by [name] desc

Result

Step 2: Use with ties

Code:
Copy codeThe Code is as follows:
Select top 3 with ties * from peopleinfo order by [name] desc

Result

If with ties is not used in combination with top and order by, the following error example is displayed:

Step 1: Do not use order by in combination with top:

Code:
Copy codeThe Code is as follows:
Select top 3 with ties * from peopleinfo

Error Message

Step 2: Do not use with top, just useOrderCombined use:

Code:
Copy codeThe Code is as follows:
Select with ties * from peopleinfo order by [name] desc

Error Message

Step 3: Do not use it with topAnd not with orderCombined use:

Code:
Copy codeThe Code is as follows:
Select with ties * from peopleinfo

Error Message