SQL Server DISTINCT – Return TOP Rows with DISTINCT on One Column

distinctsql servertop

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

Best Answer

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.

Related Question