May 15, 2006 at 12:59 pm
declare @t table (v varchar(8000),a int ,b int,c int,d int,e int)
insert @t
select 'x','1','10','100','1000','1001'
union all select 'xx','2','20','200','2000','2001'
union all select 'xxx','3','30','300','3000','3001'
union all select 'xxxx','4','40','400','4000','4001'
union all select 'xxxxx','5','50','500','5000','5001'
union all select 'xxxxxx','6','60','600','6000','6001'
union all select 'xxxxxxx','7','70','700','7000','7001'
union all select 'xxxxxxxx','8','80','800','8000','8001'
select * from @t
where a > 3
or b < 70
or e = 9001
or c > 200
For this example, i have selected based on 4 conditions (in select stmt above).
Is there an easier way to retrieve only records where atleast 2 of the 4 conditions are true.
I know I can write an AND/OR for all the possible combinations.But is there an easier way?
Thanks in advance
May 15, 2006 at 1:11 pm
select * from @t
where
CASE WHEN a > 3 THEN 1 ELSE 0 END+
CASE WHEN b < 70 THEN 1 ELSE 0 END+
CASE WHEN e = 9001 THEN 1 ELSE 0 END+
CASE WHEN c > 200 THEN 1 ELSE 0 END >=2
Vasc
May 15, 2006 at 1:31 pm
Thanks a lot Vasc!
I was trying to use CASE in select,but didnt realise this is the easier way.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply