|
|
Error Message
Server: Msg 1062, Level 15, State 1, Line 1
The TOP N WITH TIES clause is not allowed without a
corresponding ORDER BY clause.
Causes
The TOP clause specifies that only the first set of rows will be returned from the query result. The set of rows can be either a number or a percent of the rows. The TOP expression can be used in SELECT, INSERT, UPDATE, MERGE, and DELETE statements.
|
The syntax of the TOP clause is as follows:
[ TOP ( <expression> ) [ PERCENT ] [ WITH TIES ] ]
The <expression> parameter is the numeric expression that specifies the number of rows to be returned. The PERCENT clause indicates that the query returns only the first <expression> percent of rows from the result set. The WITH TIES clause specifies that additional rows be returned from the base result with the same value in the ORDER BY columns appearing as the last of the TOP n (PERCENT) rows. The TOP WITH TIES can be specified only in SELECT statements and only if an ORDER BY clause is specified. If an ORDER BY clause is not specified, this error message will be raised.
Here’s a script that illustrates how this error message can be raised:
CREATE TABLE [dbo].[WorldSeries] (
[MLBTeam] VARCHAR(50),
[Year] SMALLINT
)
GO
INSERT INTO [dbo].[WorldSeries] ( [MLBTeam], [Year] )
VALUES ( 'Boston Red Sox', 2013 )
INSERT INTO [dbo].[WorldSeries] ( [MLBTeam], [Year] )
VALUES ( 'San Francisco Giants', 2012 )
INSERT INTO [dbo].[WorldSeries] ( [MLBTeam], [Year] )
VALUES ( 'St. Louis Cardinals', 2011 )
INSERT INTO [dbo].[WorldSeries] ( [MLBTeam], [Year] )
VALUES ( 'San Francisco Giants', 2010 )
INSERT INTO [dbo].[WorldSeries] ( [MLBTeam], [Year] )
VALUES ( 'New York Yankees', 2009 )
INSERT INTO [dbo].[WorldSeries] ( [MLBTeam], [Year] )
VALUES ( 'Philadelphia Phillies', 2008 )
INSERT INTO [dbo].[WorldSeries] ( [MLBTeam], [Year] )
VALUES ( 'Boston Red Sox', 2007 )
INSERT INTO [dbo].[WorldSeries] ( [MLBTeam], [Year] )
VALUES ( 'St. Louis Cardinals', 2006 )
INSERT INTO [dbo].[WorldSeries] ( [MLBTeam], [Year] )
VALUES ( 'Chicago White Sox', 2005 )
INSERT INTO [dbo].[WorldSeries] ( [MLBTeam], [Year] )
VALUES ( 'Boston Red Sox', 2004 )
INSERT INTO [dbo].[WorldSeries] ( [MLBTeam], [Year] )
VALUES ( 'Florida Marlins', 2003 )
INSERT INTO [dbo].[WorldSeries] ( [MLBTeam], [Year] )
VALUES ( 'Anaheim Angels', 2002 )
INSERT INTO [dbo].[WorldSeries] ( [MLBTeam], [Year] )
VALUES ( 'Arizona Diamondbacks', 2001 )
INSERT INTO [dbo].[WorldSeries] ( [MLBTeam], [Year] )
VALUES ( 'New York Yankees', 2000 )
GO
SELECT TOP 3 WITH TIES [MLBTeam], COUNT(*) AS [WinCount]
FROM [dbo].[WorldSeries]
GROUP BY [MLBTeam]
GO
Msg 1062, Level 15, State 1, Line 3
The TOP N WITH TIES clause is not allowed without a corresponding ORDER BY clause.
Solution / Work Around:
As the error message suggests, and as mentioned earlier, an ORDER BY clause is required if the WITH TIES clause is included in the TOP clause of a SELECT statement. This error message can easily be resolved by specifying an ORDER BY clause. Here’s an updated script that avoids this error message:
SELECT TOP 3 WITH TIES [MLBTeam], COUNT(*) AS [WinCount]
FROM [dbo].[WorldSeries]
GROUP BY [MLBTeam]
ORDER BY COUNT(*) DESC
GO
And here’s the output of this query based on the sample data specified in the script earlier.
MLBTeam WinCount
--------------------- ---------
Boston Red Sox 3
New York Yankees 2
San Francisco Giants 2
St. Louis Cardinals 2
What the query does is return the top 3 MLB teams that have won the World Series the most since 2000 and if there’s a tie, include all teams in the result set. Since there are 3 teams that are tied with the same number of wins, all 3 tied teams are included in the result, making the number of teams in the result a total of 4.
|