Select max values from double joined table

Question

In DB i have three tables like:

+-----------------+--------+
| ident (PrimKey) | ident2 |
+-----------------+--------+
|             123 |    333 |
|             321 |    334 |
|             213 |    335 |
|            1234 |    336 |
+-----------------+--------+

+---------+----------+-------+-------+
| PrimKey | group_id | value | ident |
+---------+----------+-------+-------+
|       1 |        1 |    10 |   213 |
|       2 |        1 |     5 |   321 |
|       3 |        1 |    15 |  1234 |
|       4 |        1 |    10 |  1234 |
|       5 |        2 |     7 |   213 |
|       6 |        2 |    15 |   321 |
+---------+----------+-------+-------+

+---------+----------+----------+
| PrimKey | ident2_1 | ident2_2 |
+---------+----------+----------+
|       1 |      333 |      334 |
|       2 |      333 |      335 |
|       3 |      333 |      336 |
+---------+----------+----------+

The third table is connection between two rows from first one. And Second Contains data from different group of this rows.

I have to find max values from second table grouped by group_id for specific user connected rows from third table. In example 333. The correct answer should be:

+----------+-------+-------+
| group_id | value | ident |
+----------+-------+-------+
|        1 |    15 |  1234 |
|        2 |    15 |   321 |
+----------+-------+-------+

But for now i have all rows sorted:

+----+----------+-------+-------+
|    | group_id | value | ident |
+----+----------+-------+-------+
|  1 |        1 |    15 |  1234 |
|  2 |        1 |    10 |   213 |
|  3 |        1 |     5 |   321 |
|  4 |        2 |    15 |   321 |
|  5 |        2 |    10 |  1234 |
|  6 |        2 |     7 |   213 |
+----+----------+-------+-------+

Or correct rows with incorrect ident's

+----+----------+-------+-------+
|    | group_id | value | ident |
+----+----------+-------+-------+
|  1 |        1 |    15 |   213 |
|  2 |        2 |    15 |  1234 |
+----+----------+-------+-------+

The sql is :

DROP TABLE first;
DROP TABLE second;
DROP TABLE third;


CREATE TABLE first(group_id integer, value integer, ident integer);
CREATE TABLE second(ident integer, ident2 integer);
CREATE TABLE third(ident_1 integer, ident_2 integer);

INSERT INTO first VALUES(1, 10, 213);
INSERT INTO first VALUES(1, 5, 321);
INSERT INTO first VALUES(1, 15, 1234);

INSERT INTO first VALUES(2, 10, 1234);
INSERT INTO first VALUES(2, 7, 213);
INSERT INTO first VALUES(2, 15, 321);


INSERT INTO second VALUES(123, 333);
INSERT INTO second VALUES(321, 334);
INSERT INTO second VALUES(213, 335);
INSERT INTO second VALUES(1234, 336);

INSERT INTO third VALUES (333, 334);
INSERT INTO third VALUES (333, 335);
INSERT INTO third VALUES (333, 336);


SELECT f.group_id, max(f.value) as value, f.ident
FROM first as f
INNER JOIN second AS s ON f.ident = s.ident
INNER JOIN third AS t ON t.ident_2 = s.ident2
WHERE t.ident_1 = '333'
GROUP BY f.group_id
ORDER BY f.group_id ASC, f.value DESC;


SELECT f.group_id, f.value as value, f.ident
FROM first as f
INNER JOIN second AS s ON f.ident = s.ident
INNER JOIN third AS t ON t.ident_2 = s.ident2
WHERE t.ident_1 = '333'
ORDER BY f.group_id ASC, f.value DESC;

Tested on: https://rextester.com/l/mysql_online_compiler

Greetings

EDIT:

The third table is something like connection between friends with are rows in second table. And the first table is contain scores for different tasks with are identified by group_id. I need the best friends scores for different tasks. So list of friends i have from third table. And scores i have from first one. Connection between this tables is second one.

EDIT2:

In First table as Primary Key is ident(PrimKey).

The second and third as primary key have just another column.

In Second column ident column is index connected to ident (PrimKey) from first table.

In Third table columns ident2_1 and ident2_2 are indexes connected to indet2 from first table.

Answer 1

I came up with this SQL:

SELECT f.group_id, f.value as value, f.ident
FROM   first as f
INNER JOIN second AS s 
      ON f.ident = s.ident
INNER JOIN third AS t 
      ON t.ident_2 = s.ident2
WHERE t.ident_1 = '333'
and   f.value IN (  SELECT MAX(f1.value) 
                    FROM first as f1 
                    WHERE f1.group_id = f.group_id )
ORDER BY f.group_id ASC, f.value DESC;

Answer 2

There is surely a more elegant solution, but this will give the result you asked for.

select m.*
from(
    select f.group_id, f.value value, f.ident
    from   first f,
           second s,
           third t
    where  f.ident = s.ident
    and    t.ident_2 = s.ident2
    and    t.ident_1 = '333'
    ORDER BY f.group_id ASC, f.value DESC ) m,
(
    select max(f.value) value
    from   first f,
           second s,
           third t
    where  f.ident = s.ident
    and    t.ident_2 = s.ident2
    and    t.ident_1 = '333' ) n
where m.value = n.value

here is a second (also clunky) technique that delivers the desired results:

select f.group_id, f.value value, f.ident
from   first f,
       second s,
       third t
where  f.ident = s.ident
and    t.ident_2 = s.ident2
and    t.ident_1 = '333'
and    value = (   select max(f.value) value
                   from   first f,
                          second s,
                          third t
                   where  f.ident = s.ident
                   and    t.ident_2 = s.ident2
                   and    t.ident_1 = '333' )
ORDER BY f.group_id ASC, f.value DESC