CODEWITHSUNDEEP
mysqlsql
Bacon
Bacon

Reputation: 823

Return Rows That Share A Common Value But Another Column Must Match Multiple Criteria

I have a table that is sorted by id and value in descending order. I want to return all id's that match a group of keys in a specific order. So given (a5, a3) I want to return a and b but not d.

id  value  key
a   3      a5
a   2      a3
a   1      a4

b   4      a5
b   2      a3

c   6      a1
c   2      a2

d   4      a3
d   2      a5

The expected output would be

id
a
b

So far I've managed to match (a5, a3) but in any order. Here I'm returning all rows and fields that match in any order; not just the id.

SELECT tablename.*
FROM tablename, (SELECT * FROM tablename a
         WHERE key IN ('a5', 'a3')
         GROUP BY id
             HAVING COUNT(*) >= 1) AS result
WHERE tablename.id = result.id

Upvotes: 0

Views: 862

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

This is an example of a set-within-sets query, although it is a bit more complicated then most.

select id
from tablename t
group by id
having (max(case when "key" = 'a5' then value end) >
        max(case when "key" = 'a3' then value end)
       );

What this is doing is finding the value for "a5" and "a3" and directly comparing them. If neither is present, then the max(case . . .) will return NULL and the comparison will fail. If there is more than one value for either (or both), then it returns the largest value.

This should be pretty easy to generalize to additional keys in a particular order, by adding more similar clauses. This is why I like the aggregation with having approach to this sort of query -- it works in a lot of cases.

For the "nothing-in-between" case that you mention, I think this will work:

select id
from (select t.*, @rn := @rn + 1 as seqnum
      from tablename t cross join (select @rn := 0) const
      order by key, value
     ) t
group by id
having (max(case when "key" = 'a5' then seqnum end) = max(case when "key" = 'a3' then seqnum end) + 1
       );

The appends a sequence number and then checks that they are consecutive for your two values.

Upvotes: 1

pratik garg
pratik garg

Reputation: 3342

For this you can use following query -

select distinct t_1.ID from tablname t_1, tablename t_2 
where t_1.id = t_2.id
  and t_1.key = 'a5' and t_2.key = 'a3' 
  and t_1.value > t_2.value

Upvotes: 1

Related Questions