CODEWITHSUNDEEP
sql-server
AnandMeena
AnandMeena

Reputation: 578

Sql query to get top records with highest value of 2 columns

I have a query that is returning SUM of 2 columns with customer first name and last name.

It is returning about 40000 records. My query is :-

SELECT  SUM(Orders.BusinessVolumeTotal) AS BV,
        SUM(Orders.CommissionableVolumeTotal) AS PV,
        ISNULL(Customers.FirstName,''), Customers.LastName
FROM    Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE   Orders.OrderDate >= convert(DATETIME, '1/1/2013 12:00:00 AM')
        AND Orders.OrderDate < convert(DATETIME, '12/31/2013 12:00:00 AM')
GROUP BY Customers.FirstName, Customers.LastName

But I just want to return 10 rows with highest values in BV and PV coluymns.

Thanks in advance.

Upvotes: 1

Views: 2457

Answers (3)

Upendra Chaudhari
Upendra Chaudhari

Reputation: 6543

Based on group of two columns, you can get top 10 rows like below :

SELECT  TOP 10
        SUM(Orders.BusinessVolumeTotal) AS BV,
        SUM(Orders.CommissionableVolumeTotal) AS PV,
        ISNULL(Customers.FirstName,''), Customers.LastName
FROM    Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE   Orders.OrderDate >= convert(DATETIME, '1/1/2013 12:00:00 AM')
        AND Orders.OrderDate < convert(DATETIME, '12/31/2013 12:00:00 AM')
GROUP BY Customers.FirstName, Customers.LastName
ORDER BY SUM(Orders.BusinessVolumeTotal) + SUM(Orders.CommissionableVolumeTotal) DESC

Upvotes: 1

Tim Schmelter
Tim Schmelter

Reputation: 460028

If you want the 10 highest rows per group you can use ROW_NUMBER in a CTE:

WITH CTE AS
(
    SELECT SUM(Orders.BusinessVolumeTotal) OVER(PARTITION BY Customers.FirstName, Customers.LastName) AS BV, 
           SUM(Orders.CommissionableVolumeTotal) OVER(PARTITION BY Customers.FirstName, Customers.LastName) AS PV, 
           ISNULL(Customers.FirstName,'') As FirstName, 
           Customers.LastName,
       RN = ROW_NUMBER() 
              OVER(PARTITION BY Customers.FirstName, Customers.LastName
                   ORDER BY (Orders.BusinessVolumeTotal + Orders.CommissionableVolumeTotal) DESC) 
    FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
    WHERE  Orders.OrderDate >= convert(DATETIME, '1/1/2013 12:00:00 AM')
    AND Orders.OrderDate < convert(DATETIME, '12/31/2013 12:00:00 AM')        
)
SELECT * FROM CTE WHERE RN <= 10

Upvotes: 2

David S&#246;derlund
David S&#246;derlund

Reputation: 988

One way to get both the top 10 for BV and the top ten for PV is to write one query for each:

SELECT TOP 10
SUM(Orders.BusinessVolumeTotal) AS BV, SUM(Orders.CommissionableVolumeTotal) AS PV, ISNULL(Customers.FirstName,''), Customers.LastName
FROM         Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
 WHERE  Orders.OrderDate >= convert(DATETIME, '1/1/2013 12:00:00 AM')
                    AND Orders.OrderDate < convert(DATETIME, '12/31/2013 12:00:00 AM')
                    GROUP BY Customers.FirstName, Customers.LastName
ORDER BY BV DESC

SELECT TOP 10
SUM(Orders.BusinessVolumeTotal) AS BV, SUM(Orders.CommissionableVolumeTotal) AS PV, ISNULL(Customers.FirstName,''), Customers.LastName
FROM         Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
 WHERE  Orders.OrderDate >= convert(DATETIME, '1/1/2013 12:00:00 AM')
                    AND Orders.OrderDate < convert(DATETIME, '12/31/2013 12:00:00 AM')
                    GROUP BY Customers.FirstName, Customers.LastName
ORDER BY PV DESC

Upvotes: 0

Related Questions