CODEWITHSUNDEEP
mysqlsqldatabasejoin
C_B
C_B

Reputation: 2682

mysql - Select unique column based on max value of another column in a different table

I'm trying to select only the highest kw (model table) for each category (type table).

Model table

+-----+----+---------+
| id  | kw | type_id |
+-----+----+---------+
|   1 |  2 |       1 |
|   2 |  5 |       1 |
|   3 | 10 |       2 |
|   4 |  4 |       2 |
|   5 |  5 |       2 |
|   6 |  4 |       3 |
|   7 |  3 |       4 |
|   8 |  7 |       5 |
+-----+----+---------+

Type table

+-----+----------+
| id  | category |
+-----+----------+
|   1 |        1 | 
|   2 |        1 |
|   3 |        2 |
|   4 |        2 |
|   5 |        2 |
+-----+----------+

Attempts
1. this query returns a list of all the kws and categories:

SELECT A.kw, B.category
FROM AC_MODEL A
INNER JOIN AC_TYPE B ON A.type_id = B.id
ORDER BY A.kw DESC

2. I tried to do something like this answer but it doesn't work:

SELECT A.kw, B.category
FROM AC_MODEL A
INNER JOIN AC_TYPE B ON A.type_id = B.id
ORDER BY A.kw DESC 
WHERE (A.kw, B.category) IN (
    SELECT MAX(A.kw), B.category 
    FROM AC_MODEL A
    INNER JOIN AC_TYPE B ON A.type_id = B.id
    GROUP BY B.category
)

Does anybody have an idea?

Upvotes: 1

Views: 93

Answers (1)

Matt
Matt

Reputation: 15071

Use MAX and GROUP BY

SELECT MAX(m.kw), t.category
FROM model m
INNER JOIN type t ON m.type_id = t.id
GROUP BY t.category

OUTPUT

MAX(m.kw)   category
10          1
7           2

SQL FIDDLE: http://sqlfiddle.com/#!9/5d0df/5/0

Upvotes: 2

Related Questions