Multiple MAX values select using inner join

Question

I have query that work for me only when values in the StakeValue don't repeat. Basically, I need to select maximum values from SI_STAKES table with their relations from two other tables grouped by internal type.

SELECT a.StakeValue, b.[StakeName], c.[ProviderName] 
FROM SI_STAKES AS a 
INNER JOIN SI_STAKESTYPES AS b ON a.[StakeTypeID] = b.[ID] 
INNER JOIN SI_PROVIDERS AS c ON a.[ProviderID] = c.[ID] WHERE a.[EventID]=6 
  AND a.[StakeGroupTypeID]=1 
AND a.StakeValue IN 
  (SELECT MAX(d.StakeValue) FROM SI_STAKES AS d 
   WHERE d.[EventID]=a.[EventID] AND d.[StakeGroupTypeID]=a.[StakeGroupTypeID] 
   GROUP BY d.[StakeTypeID])
ORDER BY b.[StakeName], a.[StakeValue] DESC

Results for example must be:

[ID]  [MaxValue] [StakeTypeID] [ProviderName]
1     1,5        6             provider1
2     3,75       7             provider2
3     7,6        8             provider3

Thank you for your help

Answer 1

You can use the over clause since you're using T-SQL (hopefully 2005+):

select distinct
    a.stakevalue,
    max(a.stakevalue) over (partition by a.staketypeid) as maxvalue,
    b.staketypeid,
    c.providername
from
    si_stakes a
    inner join si_stakestypes b on
        a.staketypeid = b.id
    inner join si_providers c on
        a.providerid = c.id
where
    a.eventid = 6
    and a.stakegrouptypeid = 1

Essentially, this will find the max a.stakevalue for each a.staketypeid. Using a distinct will return one and only one row. Now, if you wanted to include the min a.id along with it, you could use row_number to accomplish this:

select
    s.id,
    s.maxvalue,
    s.staketypeid,
    s.providername
from (
    select
        row_number() over (order by a.stakevalue desc 
                           partition by a.staketypeid) as rownum,
        a.id,
        a.stakevalue as maxvalue,
        b.staketypeid,
        c.providername
    from
        si_stakes a
        inner join si_stakestypes b on
            a.staketypeid = b.id
        inner join si_providers c on
            a.providerid = c.id
    where
        a.eventid = 6
        and a.stakegrouptypeid = 1
    ) s
where
    s.rownum = 1

Answer 2

There are two problems to solve here.

1) Finding the max values per type. This will get the Max value per StakeType and make sure that we do the exercise only for the wanted events and group type.

SELECT StakeGroupTypeID, EventID, StakeTypeID, MAX(StakeValue) AS MaxStakeValue 
FROM   SI_STAKES 
WHERE  Stake.[EventID]=6 
AND    Stake.[StakeGroupTypeID]=1 
GROUP BY StakeGroupTypeID, EventID, StakeTypeID

2) Then we need to get only one return back for that value since it may be present more then once.

Using the Max Value, we must find a unique row for each I usually do this by getting the Max ID is has the added advantage of getting me the most recent entry.

SELECT MAX(SMaxID.ID) AS ID 
FROM   SI_STAKES AS SMaxID 
       INNER JOIN ( 
            SELECT StakeGroupTypeID, EventID, StakeTypeID, MAX(StakeValue) AS MaxStakeValue 
            FROM   SI_STAKES 
            WHERE  Stake.[EventID]=6 
            AND    Stake.[StakeGroupTypeID]=1 
            GROUP BY StakeGroupTypeID, EventID, StakeTypeID 
       ) AS SMaxVal ON SMaxID.StakeTypeID = SMaxVal.StakeTypeID 
                       AND SMaxID.StakeValue = SMaxVal.MaxStakeValue 
                       AND SMaxID.EventID = SMaxVal.EventID 
                       AND SMaxID.StakeGroupTypeID = SMaxVal.StakeGroupTypeID

3) Now that we have the ID's of the rows that we want, we can just get that information.

SELECT Stakes.ID, Stakes.StakeValue, SType.StakeName, SProv.ProviderName 
FROM   SI_STAKES AS Stakes 
       INNER JOIN SI_STAKESTYPES AS SType ON Stake.[StakeTypeID] = SType.[ID] 
       INNER JOIN SI_PROVIDERS AS SProv ON Stake.[ProviderID] = SProv.[ID] 
WHERE  Stake.ID IN (
            SELECT MAX(SMaxID.ID) AS ID 
            FROM   SI_STAKES AS SMaxID 
                   INNER JOIN ( 
                        SELECT StakeGroupTypeID, EventID, StakeTypeID, MAX(StakeValue) AS MaxStakeValue 
                        FROM   SI_STAKES 
                        WHERE  Stake.[EventID]=6 
                        AND    Stake.[StakeGroupTypeID]=1 
                        GROUP BY StakeGroupTypeID, EventID, StakeTypeID 
                   ) AS SMaxVal ON SMaxID.StakeTypeID = SMaxVal.StakeTypeID 
                                   AND SMaxID.StakeValue = SMaxVal.MaxStakeValue 
                                   AND SMaxID.EventID = SMaxVal.EventID 
                                   AND SMaxID.StakeGroupTypeID = SMaxVal.StakeGroupTypeID 
        )