Correlated Subqueries with MAX() and GROUP BY

Question

I have the issue using MAX() and GROUP BY. I have next tables:

personal_prizes

 ___________ ___________ _________ __________
|    id     |    userId |   specId| group    |  
|___________|___________|_________|__________|
|    1      |    1      |   1     |     1    |  
|___________|___________|_________|__________|
|    2      |    1      |   2     |     1    |  
|___________|___________|_________|__________|
|    3      |    2      |   3     |     1    |  
|___________|___________|_________|__________|
|     4     |    2      |   4     |     2    |  
|___________|___________|_________|__________|
|     5     |    1      |   5     |     2    |  
|___________|___________|_________|__________|
|     6     |    1      |   6     |     2    |  
|___________|___________|_________|__________|
|    7      |    2      |   7     |     3    |  
|___________|___________|_________|__________|


prizes
 ___________ ___________ _________ 
|    id     |    title  |   group |     
|___________|___________|_________|
|    1      |    First  |   1     | 
|___________|___________|_________|
|    2      |    Second |   1     | 
|___________|___________|_________|
|    3      |    Newby  |   1     | 
|___________|___________|_________|
|     4     |    General|   2     | 
|___________|___________|_________|
|     5     |    Leter  |   2     | 
|___________|___________|_________|
|     6     |    Ter    |   2     | 
|___________|___________|_________|
|     7     |    Mentor |   3     | 
|___________|___________|_________|

So, I need to select highest title for user. E.g. user with id = 1 must have prizes 'Second', 'Ter'. I don't know how to implement it in one query((( So, first of all, I try to select highest specID for user. I try next:

SELECT pp.specID 
FROM personal_prizes pp
WHERE pp.specID IN (SELECT MAX(pp1.id) 
                    FROM personal_prizes pp1
                    WHERE pp1.userId = 1 
                    GROUP BY pp1.group)

And it doesnt work. So please help me to solve this problem. And if you help to select prizes for user it will be great!

Answer 1

The problem I perceive here is that prizes.id isn't really a reliable way to determine which is the "highest" prize. Ignoring this however I suggest using ROW_NUMBER() OVER() to locate the "highest" prize per user as follows:

Refer to this SQL Fiddle

CREATE TABLE personal_prizes
    ([id] int, [userId] int, [specId] int, [group] int)
;

INSERT INTO personal_prizes
    ([id], [userId], [specId], [group])
VALUES
    (1, 1, 1, 1),
    (2, 1, 2, 1),
    (3, 2, 3, 1),
    (4, 2, 4, 2),
    (5, 1, 5, 2),
    (6, 1, 6, 2),
    (7, 2, 7, 3)
;


CREATE TABLE prizes
    ([id] int, [title] varchar(7), [group] int)
;

INSERT INTO prizes
    ([id], [title], [group])
VALUES
    (1, 'First', 1),
    (2, 'Second', 1),
    (3, 'Newby', 1),
    (4, 'General', 2),
    (5, 'Leter', 2),
    (6, 'Ter', 2),
    (7, 'Mentor', 3)
;

Query 1:

select
*
from (
    select
      pp.*, p.title
    , row_number() over(partition by pp.userId order by p.id ASC) as prize_order
    from personal_prizes pp
    inner join prizes p on pp.specid = p.id
    ) d
where prize_order = 1

Results:

| id | userId | specId | group | title | prize_order |
|----|--------|--------|-------|-------|-------------|
|  1 |      1 |      1 |     1 | First |           1 |
|  3 |      2 |      3 |     1 | Newby |           1 |

The result can be "reversed" by changing the ORDER BY within the over clause:

select
*
from (
    select
      pp.*, p.title
    , row_number() over(partition by pp.userId order by p.id DESC) as prize_order
    from personal_prizes pp
    inner join prizes p on pp.specid = p.id
    ) d
where prize_order = 1

| id | userId | specId | group |  title | prize_order |
|----|--------|--------|-------|--------|-------------|
|  6 |      1 |      6 |     2 |    Ter |           1 |
|  7 |      2 |      7 |     3 | Mentor |           1 |

You could expand on this logic to locate "highest prize per group" too

select
*
from (
    select
      pp.*, p.title
    , row_number() over(partition by pp.userId, p.[group] order by p.id ASC) as prize_order
    from personal_prizes pp
    inner join prizes p on pp.specid = p.id
    ) d
where prize_order = 1


| id | userId | specId | group |   title | prize_order |
|----|--------|--------|-------|---------|-------------|
|  1 |      1 |      1 |     1 |   First |           1 |
|  5 |      1 |      5 |     2 |   Leter |           1 |
|  3 |      2 |      3 |     1 |   Newby |           1 |
|  4 |      2 |      4 |     2 | General |           1 |
|  7 |      2 |      7 |     3 |  Mentor |           1 |