I have to get the second largest count in the query's result

sql, sql-server, sql-server-2008

Solution

You can use ranking functions, try it like this:

;WITH a AS (
    select pa.City,psp.Name,COUNT(he.EmployeeID) as emp_count
    from HumanResources.EmployeeAddress hea
    join HumanResources.Employee he on he.EmployeeID=hea.EmployeeID
    join Person.Contact pc on pc.ContactID=he.ContactID
    join Person.Address pa on pa.AddressID=hea.AddressID
    join Person.StateProvince psp on psp.StateProvinceID=pa.StateProvinceID
    group by pa.City,psp.Name   
), b AS (
    SELECT  *,
            ROW_NUMBER() OVER (ORDER BY emp_count DESC) num
    FROM    a
)
SELECT  *
FROM    b
WHERE   b.num = 2

Problem

I am using a test data base Advetureworks and I want to get the second highest count in the result but I'm not getting it. What changes do I have to make on the following query to get the desired result? ``` select pa.City,psp.Name,COUNT(he.EmployeeID) as emp_count from HumanResources.EmployeeAddress hea join HumanResources.Employee he on he.EmployeeID=hea.EmployeeID join Person.Contact pc on pc.ContactID=he.ContactID join Person.Address pa on pa.AddressID=hea.AddressID join Person.StateProvince psp on psp.StateProvinceID=pa.StateProvinceID where COUNT(he.EmployeeID) < (select max(count(he.employeeid)) from HumanResources.Employee) group by pa.City,psp.Name ```

Original source