Return Columns along with MAX Value

[Rotciv]

Hi all,

This is my situation

SalesReceiptId, ItemCode, ItemPrice, QuantitySold
1, AAA, 10.24, 5
1, BBB, 13.89, 6
1, CCC, 12.09, 7
1, DDD, 19.98, 2
1, EEE, 34.89, 1
2, BBB, 14.23, 63
2, CCC, 11.09, 7
3, DDD, 78.98, 2
3, EEE, 99.10, 44

I want to find out which Item in all Sales Receipts was the highest (MAX) and return the SalesReceiptId, ItemCode and QuantitySold.

For example, in the above example, i would want to return the following:-
1, EEE, 34.89, 1
2, BBB, 14.23, 63
3, EEE, 99.10, 44

Any idea? Cracking my head here.

Thanks.

[willakawill]

Hi. I am not able to figure out from what you have posted exactly which value you are calling MAX. is it the highest price, the biggest quantity sold or a combination of those?

Let me know this and we should be able to find a solution to this

[Rotciv]

My mistake, i'm actually looking for the highest sales price

[Akhilesh1505]

My mistake, i'm actually looking for the highest sales price

If You want max sales price then you can use this:
select Max(sale _price) As MaxSalePrice from TableName

[mabubakarpk]

Check this query here sales is ur requried table what ever u name

select s.* from sales s
select s.* from sales s
Where s.ItemID=(selec t top 1 itemid from Sales si where s.sid=si.sid order by price desc )

[willakawill]

I have not abandoned you rotciv. This code works:

SELECT * FROM Sales WHERE Sales.ItemPrice IN (
SELECT Max(Sales.ItemPrice) 
FROM Sales
GROUP BY Sales.SalesReceiptID);