SQL select max value with join-CodePudding

I have two table:

USER (that can have multiple profiles)

id | name | profile | ...

BET

id | profile_id | date | amount | ...

I need to select name, and the date of the bet with maximum amount betted for every user from every profile.

So the output should be something like this:

name | max_amount | date_max_amount

CodePudding user response：

USER (that can have multiple profiles)

It means that the user can have multiple ids and you would be identifying users on the basis of their names and have their max bids

SELECT u.name, MAX(b.amount) 
FROM USER u
LEFT JOIN BET b ON (u.id = b.profile_id)
GROUP BY u.name

Then you can

SELECT u.name, d.max_amount, b.date AS date_max_amount
FROM USER u
LEFT JOIN (
    SELECT u.name, MAX(b.amount) as max_amount
    FROM USER u
    LEFT JOIN BET b ON (u.id = b.profile_id)
    GROUP BY u.name
)d ON (u.name = d.name)
LEFT JOIN BET b ON (u.id = b.profile_id AND b.amount = d.max_amount)

CodePudding user response：

We can do it via GROUP BY and INNER JOIN

SELECT name, b2.amount as max_amount,b.1date as date_max_amount
FROM
  user u
JOIN bet b1 ON u.profile=b1.profile_id
JOIN
(SEELCT max(amount) as cnt,profile
  FROM bet 
  GROUP BY profile_id) b2
ON b1.date=b2.date AND b1.amount=b2.amount AND b1.profile_id=b2.profile_id