WITH TIES allows you to return more rows with values that match the last row in the limited result set. Note that
WITH TIES may cause more rows to be returned than you specify in the expression.
The selection of which the rows to return is nondeterministic.
This means that if you run the query again, without the underlying data changing, theoretically you could get a different set of three rows.
In practice, the row selection will depend on physical conditions like :
- optimization choices
- storage engine choices
- data layout
If you actually run the query multiple times, as long as those physical conditions don’t change, there’s some likelihood you will keep getting the same results. But it is critical to understand the “physical data independence” principle from the relational model, and remember that at the logical level you do not have a guarantee for repeatable results. Without ordering specification, you should consider the order as being arbitrary, resulting in a nondeterministic row selection
If you want to use TOP WITH TIES you must use order by.
CREATE TABLE [dbo].[Products]( [Id] [int] IDENTITY(1,1) NOT NULL, [ProductName] [nvarchar](50) NULL, [Price] [float] NULL) GO
The following illustrates the INSERT statement that inserts rows into an existing table
INSERT INTO [dbo].[Products] VALUES ('Bicycle 1' , 258.2) INSERT INTO [dbo].[Products] VALUES ('Bicycle 2' , 265.3) INSERT INTO [dbo].[Products] VALUES ('Bicycle 3' , 267.8) INSERT INTO [dbo].[Products] VALUES ('Bicycle 4' , 268.9) INSERT INTO [dbo].[Products] VALUES ('Bicycle 5' , 267.9) INSERT INTO [dbo].[Products] VALUES ('Bicycle 6' , 267.9) GO
SELECT TOP 4 WITH TIES ProductName, Price FROM Products ORDER BY Price
In this example, the two expensive product has a list price of 267.9. Because the statement used TOP WITH TIES, it returned one more products whose list prices are the same as the forth one.
If you run the normal top 4 then the result set will be
select top 4 ProductName, Price FROM Products order by price
Post a Comment