CODEWITHSUNDEEP
sqljoinmax
SavageCoder
SavageCoder

Reputation: 81

Select Max value and bring other data from that row

I am trying to find the seller with the most "ItemsSold" and display that seller and everything else in that row. For some reason, my query keeps pulling the whole table.

SELECT DISTINCT
   Name,
   Username,
   Password,
   Address,
   Email,
   CCNumber,
   CCExpiration,
   ItemsSold
FROM auctionsite.dbo.Seller
JOIN (SELECT MAX(ItemsSold) AS id FROM auctionsite.dbo.Seller GROUP BY 
   ItemsSold) max ON id = max.id

Upvotes: 0

Views: 24

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

You can use order by and logic to fetch one row:

select s.*
from auctionsite.dbo.Seller s
order by s.itemSold desc
fetch first 1 row only;

Note: if there are ties, then this fetches only one of the tied rows.

If you want all ties, one method uses a subquery:

select s.*
from auctionsite.dbo.Seller s
where s.itemSold = (select max(s2.itemSold) from auctionsite.dbo.Seller s2);

Upvotes: 1

Related Questions