Auxiliary Numbers Table - Populated by CTE (Based on Itzik Ben-Gan)

USE [Auxiliary]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE  [dbo].[Nums]
([n] [int] NOT NULL, PRIMARY KEY CLUSTERED
 ([n]  ASC ))
GO
;WITH x00(n) AS (SELECT 1 UNION ALL SELECT 1),
x02 (n) AS (SELECT 1 FROM x00 a, x00 b),
x04 (n) AS (SELECT 1 FROM x02 a, x02 b),
x08 (n) AS (SELECT 1 FROM x04 a, x04 b),
x16 (n) AS (SELECT 1 FROM x08 a, x08 b),
x32 (n) AS (SELECT 1 FROM x16 a, x16 b),
cTally (n) AS (SELECT ROW_NUMBER() OVER (ORDER BY n) FROM x32)
 
INSERT INTO Nums(n)
SELECT * from cTally
WHERE n <= 1000000;
GO



 Based on the CTE used here:
http://sqlreportingservicescrystalreports.blogspot.co.uk/2009/12/auxilary-cte-of-numbers-sql.html

Row numbering in TSQL 2005 - SQL

New to SQL Server 2005 is ROW_NUMBER function.

SELECT
JobLog.JobLogID,
Establishment.EstablishmentName,
JobSubType.JobSubTypeDescription,
JobLog.JobLogSignOffTimeStamp,
ROW_NUMBER() OVER(ORDER BY EstablishmentName ) AS rownumNonDeterministic,
ROW_NUMBER() OVER(ORDER BY EstablishmentName,JobLog.JobLogSignOffTimeStamp ) AS rownumDeterministic,
ROW_NUMBER() OVER(PARTITION BY EstablishmentName ORDER BY EstablishmentName,JobLog.JobLogSignOffTimeStamp ) AS rownumPartitioning
FROM JobLog
INNER JOIN
Establishment ON JobLog.JobLog_EstablishmentID = Establishment.EstablishmentID  
INNER JOIN
JobSubType ON JobLog.JobLog_JobSubTypeID = JobSubType.JobSubTypeID
WHERE JobLog.JobLogSignOff = '1'
ORDER BY EstablishmentName,JobLog.JobLogSignOffTimeStamp

Nth largest value - SSRS

I recently had a query regarding replicating Crystal Reports nth largest functionality in Reporting Services (hope this helps, Andy!).

For example, In Crystal Reports, its simply a case of using Insert>>Summary

I believe the most efficient way to go about is to use a subquery in T-SQL and incorporate into a dataset.

Using the AdventureWorks db, the first query uses the ROW_NUMBER function to sequentially order rows, purely for this examples sake. (ROW_NUMBER is only available in SQL Server 2005 onwards)

SELECT SalesPersonID, SalesYTD, ROW_NUMBER() OVER(ORDER BY SalesYTD desc) as rownum
FROM AdventureWorks.Sales.SalesPerson

The second query, using a subquery, firstly selects the top 3 SalesYTD values and then selects the smallest of these 3, the third largest of all values.

SELECT TOP 1 SalesPersonID, SalesYTD FROM
(SELECT TOP 3 SalesPersonID, SalesYTD FROM AdventureWorks.Sales.SalesPerson ORDER BY SalesYTD desc) as Sales
ORDER BY SalesYTD asc


By simply replacing the 3 in the subquery by n, the nth largest value can be obtained.