CODEWITHSUNDEEP
sqlt-sql
Ulli
Ulli

Reputation: 510

T-Sql: Select Rows where at least two fields matches condition

I've got a table, let's call it values with a primary key and five integer fields, like this:

id  val1  val2  val3  val4  val5
1    4     3     4     5     3
2    2     3     2     2     2
3    5     4     1     3     3
4    1     4     3     4     4

Now I need to select all rows where at least any two of the five value fields got the value 4. So the result set should contain the first row (id=1) and the last row (id=4).

I started with a simple OR condition but there are too many combinations. Then I tried a sub-select with HAVING and COUNT but no success.

Any Ideas how to solve this?

Upvotes: 0

Views: 2138

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

Although cross apply is fast, it might be marginally faster to simply use case:

select t.*
from t
where ((case when val1 = 4 then 1 else 0 end) +
       (case when val2 = 4 then 1 else 0 end) +
       (case when val3 = 4 then 1 else 0 end) +
       (case when val4 = 4 then 1 else 0 end) +
       (case when val5 = 4 then 1 else 0 end)
      ) >= 2;

I will also note that case is ANSI standard SQL and available in basically every database.

Upvotes: 6

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239824

This is trivial to solve if your data is normalized - so lets use UNPIVOT to normalize the data and then solve it:

declare @t table (id int not null, val1 int not null, val2 int not null,
                  val3 int not null, val4 int not null, val5 int not null)
insert into @t(id,val1,val2,val3,val4,val5) values
(1,4,3,4,5,3),
(2,2,3,2,2,2),
(3,5,4,1,3,3),
(4,1,4,3,4,4)

select
    id
from
    @t t
        unpivot
    (valness for colness in (val1,val2,val3,val4,val5)) r
group by id
having SUM(CASE WHEN valness=4 THEN 1 ELSE 0 END) >= 2

Results:

id
-------
1
4

Of course, you can probably come up with better names than valness and colness that describes what these pieces of data (the numbers being stored and the numbers embedded in the column names) actually are.

Upvotes: 2

Giorgos Betsos
Giorgos Betsos

Reputation: 72225

You can use VALUES to construct an inline table containing your fields. Then query this table to get rows having at least two fields equal to 4:

SELECT *
FROM mytable
CROSS APPLY (
  SELECT COUNT(*) AS cnt
  FROM (VALUES (val1), (val2), (val3), (val4), (val5)) AS t(v)
  WHERE t.v = 4) AS x
WHERE x.cnt >= 2

Demo here

Upvotes: 9

Related Questions