9

How do you query for three unique customers with the largest Purchase_Cost?

I want to apply the DISTINCT only on Customer_Name, but the query below applies the distinct on all three columns. How should I modify the query to obtain the desired output?

SELECT DISTINCT TOP 3 customer_name, order_no, Purchase_Cost  
FROM PurchaseTable 
ORDER BY Purchase_Cost

enter image description here

9

Replace your dbname and schemaName in the following query.

;WITH CTE AS 
(
SELECT  
       [Order_No]
      ,[Customer_Name]
      ,[Purchase_Cost]
     , ROW_NUMBER() OVER(PARTITION BY [customer Name] ORDER BY [Purchase Cost] DESC) AS "RowNumber"
  FROM [dbname].[schemaName].[PurchaseTable]
  )

  SELECT TOP(3)
       [Order_No]
      ,[Customer_Name]
      ,[Purchase_Cost]
  FROM CTE WHERE RowNumber=1
  ORDER BY [Purchase_Cost] DESC

I am sure there are other ways of doing the same. I suggest you read this.

| improve this answer | |
1

please try:

SELECT DISTINCT TOP 3  order_no, customer_name,  Purchase_Cost
FROM
(   SELECT order_no, customer_name, Purchase_Cost, ROW_NUMBER() OVER(PARTITION BY customer_name ORDER BY Purchase_Cost DESC) Orders
    FROM PurchaseTable
) A
WHERE A.Orders = 1
ORDER BY Purchase_Cost DESC
| improve this answer | |
0

I think this could be more efficient that the other solutions, provided you have an index on: (Customer_Name, Purchase_Cost DESC) INCLUDE (Order_No)

;
WITH PurchaseTable AS
(
    SELECT * 
      FROM (VALUES ((501),('Carson'),(3400)),
                   ((502),('Thomas'),(625)),
                   ((503),('Daisy'),(4856)),
                   ((504),('Mary'),(2397)),
                   ((505),('Carson'),(5000))
           ) AS T(Order_No,Customer_Name,Purchase_Cost)
),
DistinctCustomers AS
(
    SELECT DISTINCT
           Customer_Name
      FROM PurchaseTable
)
SELECT TOP(3) 
       MaxCustomerOrder.Order_No,
       dc.Customer_Name,
       MaxCustomerOrder.Purchase_Cost
  FROM DistinctCustomers dc
 CROSS APPLY (SELECT TOP(1) * 
                FROM PurchaseTable pt 
               WHERE pt.Customer_Name = dc.Customer_Name 
               ORDER BY pt.Purchase_Cost DESC
             )    AS MaxCustomerOrder
 ORDER BY MaxCustomerOrder.Purchase_Cost DESC
;

Or without the inline table definition:

;
WITH DistinctCustomers AS
(
    SELECT DISTINCT
           Customer_Name
      FROM PurchaseTable
)
SELECT TOP(3) 
       MaxCustomerOrder.Order_No,
       dc.Customer_Name,
       MaxCustomerOrder.Purchase_Cost
  FROM DistinctCustomers dc
 CROSS APPLY (SELECT TOP(1) * 
                FROM PurchaseTable pt 
               WHERE pt.Customer_Name = dc.Customer_Name 
               ORDER BY pt.Purchase_Cost DESC
             )    AS MaxCustomerOrder
 ORDER BY MaxCustomerOrder.Purchase_Cost DESC
;
| improve this answer | |
  • The first query does not look valid. – dezso Jun 29 '17 at 12:09
  • I tried to fix it. Not sure what the difference is between query 1 and 2. – ypercubeᵀᴹ Jun 29 '17 at 12:29
  • Query 2 just has the inline table data removed. You don't need it if you actually have the table with the data in it. If you don't have the actual table, like me, you would need it to test that it runs. – Jonathan Roberts Jun 29 '17 at 16:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.