Introducing Microsoft SQL Server 2012 - EBook Download

The full version of "Introducing Microsoft SQL Server 2012" by "Ross Mistry” and “Stacia Misner” is now available for free download,

Download Introducing Microsoft SQL Server 2012 

"Introducing Microsoft SQL Server 2012" includes 10 chapters:

PART I - DATABASE ADMINISTRATION (by Ross Mistry)
1. SQL Server 2012 Editions and Engine Enhancements
2. High-Availability and Disaster-Recovery Enhancements
3. Performance and Scalability
4. Security Enhancements
5. Programmability and Beyond-Relational Enhancements

PART II - BUSINESS INTELLIGENCE DEVELOPMENT(by Stacia Misner)
6. Integration Services
7. Data Quality Services
8. Master Data Services
9. Analysis Services and PowerPivot
10.Reporting Services

 

Download Introducing Microsoft SQL Server 2012

Find Nth Highest Value – With Using Only Aggregate Function

Problem:

Today I came across a SQL Puzzle, “How to Find Nth Highest value without using any Built in ANSI Functions like Top, Rank() , Dense_Rank(), Row_number(), Etc., you can use only aggregate functions like Min, Max and Count ?”

Solution:

The following queries will help you to identify Nth highest value with using Only Aggregate Functions.

Note: I’ve used Sales.SalesOrderDetail Table from adventure Works Database in my script, you can replace the column and table name as per your table structure.

Use AdventureWorks

--- To Find Nth Highest Value, Replace N with Some Number in the given Query, For Example, if you want to find 12th Highest value, Use 12 Instead of "N"

SELECT distinct UnitPrice

FROM Sales.SalesOrderDetail E1

WHERE N = 

          (

            SELECT COUNT(DISTINCT E2.UnitPrice)

            FROM Sales.SalesOrderDetail E2

            WHERE E2.UnitPrice >= E1.UnitPrice

          )

---- following Query will returns distinct values in Descending order, you can refer this Query’s output to verify Nth value of previous query output.

select distinct UnitPrice from Sales.SalesOrderDetail A

order by UnitPrice desc

Hope it Helps!