TOP Statement

• WITH TIES specifies that additional rows will be returned from the base result set.
  • The additional rows will have the same value in the ORDER BY columns appearing as the last of the TOP n (PERCENT) rows.
  • TOP...WITH TIES can be specified only in SELECT statements, and only if an ORDER BY clause is specified.
• Suppose we have 100 rows in the table and out of that, 50 rows have same value.
  • When you use TOP 10 rows, it will return you only 10 rows.
  • If you use TOP 10 WITH TIES, it will return you all the rows that have same value as that of the last record of top 10.
    • Which means a total of 50 records.

SELECT TOP 10 * FROM [AdventureWorks].[Person].[Person];

DECLARE @Limit AS INT = 6;
SELECT TOP (@Limit) * 
FROM [AdventureWorks].[Person].[Person];

SET @Limit = 4;
SELECT TOP (@Limit) WITH TIES * 
FROM [AdventureWorks].[Person].[Person]
ORDER BY [LastName];

SELECT TOP (SELECT COUNT(*) FROM [AdventureWorks].[Person].[Address]) *
FROM [AdventureWorks].[Person].[Person];

UPDATE TOP (@Limit) [AdventureWorks].[Person].[Person]
SET [LastName] = [LastName] + 'Test'
WHERE BusinessEntityID = 3;

• The order in which rows are returned in a result set are not guaranteed unless an ORDER BY clause is specified.
• In a SELECT TOP (N) statement, always use an ORDER BY clause.
  • This is the only way to predictably indicate which rows are affected by TOP.

Pagination After SQL Server 2012

DECLARE
	@RowsPage AS INT = 20,
	@PageNumber AS INT = 10;

SELECT *
FROM [Test]
ORDER BY [Id]
OFFSET (@PageNumber - 1) * @RowsPage ROWS
FETCH NEXT @RowsPage ROWS ONLY;

Pagination Before SQL Server 2012

DECLARE
	@RowsPage AS INT = 20,
	@PageNumber AS INT = 10;

SELECT 
	[Id],
	[Name]
FROM
(
	SELECT 
		ROW_NUMBER() OVER(ORDER BY [Id]) AS [Number],
		[Id], 
		[Name]
	FROM [Test]
) AS [Tbl]
WHERE [Number] BETWEEN ((@PageNumber - 1) * @RowsPage + 1) AND (@PageNumber * @RowsPage)
ORDER BY [Id]

RANK

• It returns the rank of each row within the partition of a result set.
• The rank of a row is "one plus the number of ranks that come before the row in question".
  • If two or more rows tie for a rank, each tied rows receives the same rank.
• For example, if the two top salespeople have the same SalesYTD value, they are both ranked one.
  • The salesperson with the next highest SalesYTD is ranked number three, because there are two rows that are ranked higher.
  • Therefore, the RANK function does not always return consecutive integers.

Ranking rows within a partition

• The following example ranks the products in inventory the specified inventory locations according to their quantities.
  • The result set is partitioned by LocationID and logically ordered by Quantity.

SELECT
	RANK() OVER (PARTITION BY [LocationID] ORDER BY [Quantity] DESC) AS [Rank],
	[LocationID],
	[Quantity]
FROM Production.ProductInventory
WHERE LocationID BETWEEN 3 AND 4
ORDER BY [LocationID], [Quantity] DESC;

DENSE_RANK

• It returns the rank of rows within the partition of a result set, without any gaps in the ranking.
  • The rank of a row is one plus the number of distinct ranks that come before the row in question.