D J Horton Consulting Ltd

Personal reference to useful tips and tricks for SQL Server, SQL Server Reporting Services and Crystal Reports.

My alternative to scraps of paper lying about.

Wednesday, 5 May 2010

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 3 SalesPersonID, SalesYTD FROM AdventureWorks.Sales.SalesPerson ORDER BY SalesYTD desc) as Sales

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

1 comment:

Andy said...

I don't have much experience writing and understanding what the SQL syntax is all about. (I'm used to Crystal Reports after all) This solution forced me to educate myself a bit.

I did manage to get the solution implemented, and solved my immediate problem of returning the nth highest value, and increased my understanding of SQL from about a 1 to a 3.