CODEWITHSUNDEEP
sqlsql-serversql-server-2008distinct
Rohit Saluja
Rohit Saluja

Reputation: 1517

SQL Server : TOP along with Distinct

I have two tables Products and PurchaseDetails.

The schema for Products table is 

ProductId  (primary key)
ProductName
CategoryId
Price
QuantityAvailable

The schema for PurchaseDetails table is 

PurchaseId
EmailId
ProductId
QuantityPurchased
DateOfPurchase

The question asks me to find out the TOP 3 products that are purchased in large quantity.

I wrote this SQL query:

Select TOP 3 
    Distinct(ProductName), Price, QuantityPurchased
from 
    Product, PurchaseDetails
where 
    Product.ProductId = PurchaseDetails.ProductId
order by 
    QuantityPurchased DESC

But the above query throws an error. I fail to see why the error is being generated by the above query ?

Upvotes: 0

Views: 87

Answers (2)

Shiraj Momin
Shiraj Momin

Reputation: 685

Select TOP 3 ProductName,sum(Price) as [price],sum(QuantityPurchased) as QuantityPurchased
    from Product , PurchaseDetails
    where Product.ProductId=PurchaseDetails.ProductId
    group by ProductName
    order by QuantityPurchased DESC

Upvotes: 0

Shiju Shaji
Shiju Shaji

Reputation: 1730

Below query will give you the top 3 products that are purchased in large quantity 

Select TOP 3 ProductName,sum(Price) as [price],sum(QuantityPurchased) as QuantityPurchased
    from Product , PurchaseDetails
    where Product.ProductId=PurchaseDetails.ProductId
    group by ProductName
    order by QuantityPurchased DESC

Upvotes: 1

Related Questions