CODEWITHSUNDEEP
mysqlsql
MCterra
MCterra

Reputation: 43

Select a key value from MySQL table where one row with the key is greater than another

So assume I have this table:

+-------+-------+--------+--------+
|  key  |  id   |  type  |  value |
+-------+-------+--------+--------+
|   1   |   1   |  cool  |    5   |
|   2   |   1   |  uncool|    10  |
|   3   |   2   |  none  |    7   |
|   4   |   2   |  cool  |    23  |
|   5   |   2   |  uncool|    4   |
|   6   |   3   |  cool  |    6   |
|   7   |   3   |  uncool|    3   |
|   8   |   3   |  unapp |    52  |
+-------+-------+--------+--------+

Note that the key column is just the unique identifier, and is not relevant for what I am trying to do.

I want to find the ids (key) where the cool type value is greater than the uncool type value, ignoring any other rows. So the result would be

+------+
|  id  |
+------+
|  2   |
|  3   |
+------+

I spent a few hours with the SQL editor open, messing with HAVING and GROUP BY and haven't been able to find a solution. Is there a way to do this without resorting to making queries and then performing checks in another language, or is this the only solution?

Upvotes: 0

Views: 871

Answers (2)

Akina
Akina

Reputation: 42622

SELECT t1.id
FROM table t1
LEFT JOIN table t2 ON t1.id = t2.id
                  AND t2.type = 'uncool'
WHERE t1.type = 'cool'
  AND (t1.value > t2.value OR t2.value IS NULL)

fiddle

If uncool is not present (for example, remove row with key=5) then my solution will give 2 rows whereas another solutions will give 1 row only.

Upvotes: 1

juergen d
juergen d

Reputation: 204756

You can group by the id and take only those having the condition you describe.

select id
from your_table
group by id
having max(case when `type` = 'cool' then `value` end) > 
       max(case when `type` = 'uncool' then `value` end)

Upvotes: 1

Related Questions