Bishawjit Das Bishawjit Das - 4 months ago 12
MySQL Question

SQL SELECT only rows having MAX value of a column from two different tables

My two table setup is like below:

table1


+------+---------+--------------------------------------+
| id | tail | content |
+------+---------+--------------------------------------+
| 1 | abc | ... |
| 2 | def | ... |
| 3 | ghi | ... |
| 4 | def | ... |
| 5 | jkl | ... |
+------+-------+----------------------------------------+


table2


+------+--------+---------------------------------------+
| id | tailID | value | others |
+------+--------+---------------------------------------+
| 1 | 2 | 412 | |
| 2 | 3 | 215 | |
| 1 | 2 | 571 | |
| 1 | 4 | 123 | |
+------+--------+---------------------------------------+


I like to get all columns from this two tables in a row with matched tail = tailID but not duplicate rows which has same tail.

For the duplicate TAIL, just need to get the single row of max VALUE of same tail.

I am currently using


SELECT table1.tail, table2.other_column
FROM table1
INNER JOIN table2
on table1.id = table2.tailID
WHERE table1.some_coloum = "a sepecific string"
ORDER BY table2.value


But it returns many duplicates of same tail.

I just need to have single row for duplicate TAIL with hightes VALUE of table2.

Answer

DISTINCT with CROSS APPLY:

SELECT DISTINCT t1.tail,
                t2.other_column,
                t3.[value]
FROM table1 t1
CROSS APPLY (
    SELECT  tailid,
            MAX([value]) as [value]
    FROM table2
    WHERE tailid = t1.id
    GROUP BY tailid
    ) as t3
INNER JOIN table2 t2
    ON t2.tailid = t3.tailid AND t3.[value] = t2.[value]
WHERE t1.some_coloum = "a sepecific string"