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

Question

My setup of the two tables is as follows:

Table 1

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

table 2

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

I like to get all the columns from these two tables in a row with matching tail = tailID, but not with duplicate rows that have the same tail.

For a duplicate TAIL, you just need to get one max VALUE line of the 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 the same tail.

hightes VALUE 2.

Answer 1

DISTINCT 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" 

Answer 2

, MAX value

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

Answer 3

The first group table2 then joins

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

A query can still return multiple rows for a row table1if table2there are two or more rows with the same max(value)and tailID.