SQL SELECT仅包含两个不同表中列的MAX值的行

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.

我喜欢将这两个表中的所有列连接到匹配的tail = tailID,但不是具有相同尾部的重复行。

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

对于重复的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.

我只需要有一行重复TAIL,其高级值为table2。

3 个解决方案

#1

DISTINCT with CROSS APPLY:

DISTINCT与交叉申请:

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"

#2

Selected only rows where is MAX value of column value

仅选择行值为MAX值的行

SELECT table1.tail, MAX(table2.value)
FROM
  table1
  INNER JOIN table2 ON table1.id = table2.tailID
  WHERE table1.content = "test"

http://sqlfiddle.com/#!9/b70d29/3/0

#3

First group table2 then join

然后第一组table2加入

SELECT table1.tail, table2.other_column 
FROM table1 
INNER JOIN (
   SELECT tailID, max(value) as value
   FROM table2
   GROUP BY tailID
) t2g ON t2g.tailID = table1.ID
INNER JOIN table2 
on t2g.tailID = table2.tailID AND  t2g.value = table2.value
WHERE table1.some_coloum = "a sepecific string" 
ORDER BY table2.value

The query still may return multiple rows for a table1 row if there are 2 or more rows in table2 with the same max(value) and tailID.

如果table2中有2行或更多行具有相同的max(value)和tailID,则查询仍可能为table1行返回多行。

#1