CODEWITHSUNDEEP
sqlazure-sql-database
Fakhruddin Bandukwala
Fakhruddin Bandukwala

Reputation: 18

Query to find the max of a value with multiple group by attributes

I am trying to get the max bidamt on a particular item and display the user who has bid that amount

I am able to get the results in two different queries each returning a subset of what I want

select username, auctionbids.itemid, description, bidamt from AuctionBids 
inner join users on auctionbids.userid = users.id
inner join auctionitems on auctionbids.itemid = auctionitems.itemid

select ab.itemid,max(ab.bidamt) as bidmax from auctionbids as ab group by ab.itemid

I want to get username, itemid, item descripton, max(bidamt) joining three tables - users, auctionitems, auctionbids.

users contains userid and username

auctionitems contains itemid and item description

auctionbids contains userid, itemid, bidamt

Upvotes: 0

Views: 62

Answers (4)

MJoy
MJoy

Reputation: 1369

The following query should do what you want:

SELECT a.item_description,a.username,a.bidamt FROM 
(
SELECT au.item_description,u.username,ab.bidamt, RANK() OVER(PARTITION BY au.itemid ORDER BY ISNULL(ab.bidamt,0) DESC) AS [Rank]
FROM auctionitems au
LEFT JOIN auctionbids ab ON au.itemid = ab.itemid
LEFT JOIN users u ON ab.userid = u.userid ) a WHERE [Rank] = 1

Upvotes: 1

Fakhruddin Bandukwala
Fakhruddin Bandukwala

Reputation: 18

Thanks for the ideas which led to this query -

SELECT am.*, auctionbids.submitted, users.username, auctionitems.description 
FROM
(SELECT ab.itemid, max(ab.bidamt) as bidmax 
FROM 
auctionbids ab
GROUP BY ab.itemid) AS am
   INNER JOIN auctionbids ON am.itemid = auctionbids.itemid and am.bidmax = auctionbids.bidamt
    INNER JOIN users ON auctionbids.userid = users.id
    INNER JOIN auctionitems ON auctionbids.itemid = auctionitems.itemid

So basically the max has to be found with a group by on itemid and then the inner join with rest of the tables to fetch the attributes.

Upvotes: 0

Leon Yue
Leon Yue

Reputation: 16431

As you said, the two different queries each returning a subset of what you want, your query should like this:

SELECT am.username, am.itemid, am.descripton, max(am.bidamt) AS bidmax
FROM (
    SELECT username, auctionbids.itemid, auctionitems.description, bidamt FROM AuctionBids 
    INNER JOIN users ON auctionbids.userid = users.id
    INNER JOIN auctionitems ON auctionbids.itemid = auctionitems.itemid
) AS am 
GROUP BY am.itemid

Upvotes: 1

artemis
artemis

Reputation: 7281

Without example data, it is impossible to verify this works correctly. But you will need to match the bidamt with the MAX bidamt for a particular item.

SELECT DISTINCT
    users.username,
    auctionbids.itemid,
    auctionitems.description,
    auctionbids.bidamt

FROM
    users
        INNER JOIN auctionbids on users.userid = auctionbids.userid
        INNER JOIN auctionitems on auctionbids.itemid = auctionitems.itemid

WHERE
    auctionbids.bidamt = (SELECT MAX(bidamt) OVER (PARTITION BY itemid, description))
    AND
    itemid = < whatever you want >

Upvotes: 0

Related Questions