June 26, 2014 at 2:29 pm
Is there an elegant way to solve the question "I only want to show widgets that have a value "Y" and only if all the entries for widget have a value "Y".
here is some test data
select 'A' marker, 'Y' val
into TEMP_A
union all
select 'B', 'N'
union all
select 'B', 'Y'
select * from TEMP_A
markerval
AY
BN
BY
What I want is a single query that will only return 'A' (it is the only marker that has a val of 'Y' and only 'Y')
<><
Livin' down on the cube farm. Left, left, then a right.
June 26, 2014 at 2:39 pm
May well be a more efficient way to do this, but here's a shot!
select marker from TEMP_A
WHERE val = 'Y'
EXCEPT
SELECT marker FROM TEMP_A
WHERE val = 'N'
- 😀
June 26, 2014 at 3:09 pm
Here is another way to do this. This method prevents accessing the table a second time. Not sure it is more efficient in your real world situation. It does however make this a bit easier if you have more values than 'Y' and 'N' in your actual table.
select marker
, MIN(val) as val
from TEMP_A
group by marker
having MIN(val) = MAX(val)
and MIN(val) = 'Y'
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 26, 2014 at 3:18 pm
Here's another alternative that should perform well as it not only uses just one pass on the table, but uses NULLIF which usually is rather fast:
SELECT marker
FROM TEMP_A
GROUP BY marker
HAVING COUNT(NULLIF(val, 'N')) = COUNT(*);
As COUNT will only look at non-NULL values, NULLIF is an easy way to turn non-desired values into NULL, and thus return only the groups where the counts are equal, which can only happen when there are no 'N' values in the group.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 26, 2014 at 3:23 pm
Aha! I was fumbling with trying to come up with some sort of aggregate to avoid a double-pass on the table, but I couldn't figure it out. I'll just file those handy tricks away in the experimentation folder 😀
- 😀
June 26, 2014 at 3:50 pm
Here is another option, requires two passes of the table though.
create table #Temp(Marker char(1), val char(1));
insert into #Temp
values ('A','Y'),('B','N'),('B','Y');
select
*
from
#Temp t
where
not exists(select
1
from
#Temp t1
where
t.Marker = t1.Marker and
t1.val <> 'Y');
drop table #Temp;
June 26, 2014 at 3:59 pm
And here, using the minimal sample set, are the execution plans for the suggested solutions.
create table #Temp(Marker char(1), val char(1));
insert into #Temp
values ('A','Y'),('B','N'),('B','Y');
select
*
from
#Temp t
where
not exists(select
1
from
#Temp t1
where
t.Marker = t1.Marker and
t1.val <> 'Y');
select
Marker,
MIN(val) as val
from
#Temp
group by
Marker
having
MIN(val) = MAX(val)
and MIN(val) = 'Y';
SELECT
Marker
FROM
#Temp
GROUP BY
Marker
HAVING
COUNT(NULLIF(val, 'N')) = COUNT(*);
select
Marker
from
#Temp
WHERE
val = 'Y'
EXCEPT
select
Marker
from
#Temp
WHERE
val = 'N';
drop table #Temp;
June 26, 2014 at 4:17 pm
With the same requirements, if you add a Marker with a third value for val, one of the options provided fails. Run this:
create table #Temp(Marker char(1), val char(1));
insert into #Temp
values ('A','Y'),('B','N'),('B','Y'),('C','Z');
select
*
from
#Temp t
where
not exists(select
1
from
#Temp t1
where
t.Marker = t1.Marker and
t1.val <> 'Y');
select
Marker,
MIN(val) as val
from
#Temp
group by
Marker
having
MIN(val) = MAX(val)
and MIN(val) = 'Y';
SELECT
Marker
FROM
#Temp
GROUP BY
Marker
HAVING
COUNT(NULLIF(val, 'N')) = COUNT(*);
select
Marker
from
#Temp
WHERE
val = 'Y'
EXCEPT
select
Marker
from
#Temp
WHERE
val = 'N';
drop table #Temp;
June 26, 2014 at 4:54 pm
EDIT: Yep, Lynn, that would be mine. Oops... that's not right. It's the queries using MAX and MIN. The 'Z' value would throw those queries off. END EDIT
My query IS dependent on the initial conditions, but with so little detail on what the real world scenario might be, I try not to make up new conditions, and try to rely on some level of common sense on the part of the original poster. If it then fails because the actual conditions are other than what was posted, then the OP learns something about how to describe a problem in sufficient detail, whereas if I try to cover all possible angles, the OP often learns relatively little, as this often ends up with post after post after post of questions and non-answers until one side or the other either finally figures it out or gives up.
EDIT: Finally, with 'Y' and 'N' as the values for "val", I think it's a reasonably safe assumption that there are no other values for that field, making it safe to use MAX and MIN or other binary choice methods. At least as safe as one can be given the nature of the forum, anyway... :-):-)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 26, 2014 at 5:02 pm
sgmunson (6/26/2014)
Yep, Lynn, that would be mine. It IS dependent on the initial conditions, but with so little detail on what the real world scenario might be, I try not to make up new conditions, and try to rely on some level of common sense on the part of the original poster. If it then fails because the actual conditions are other than what was posted, then the OP learns something about how to describe a problem in sufficient detail, whereas if I try to cover all possible angles, the OP often learns relatively little, as this often ends up with post after post after post of questions and non-answers until one side or the other either finally figures it out or gives up.Finally, with 'Y' and 'N' as the values for "val", I think it's a reasonably safe assumption that there are no other values for that field. At least as safe as one can be given the nature of the forum, anyway... :-):-)
You are correct, many times we get only a partial explanation of the problem and our solutions don't always work when they get put into a more robust test environment (hopefully) or into production (I really hope not).
I was just doing a what if there were other values and the OP still wanted only those rows with a specific value.
June 26, 2014 at 5:10 pm
Sean Lange (6/26/2014)
Here is another way to do this. This method prevents accessing the table a second time. Not sure it is more efficient in your real world situation. It does however make this a bit easier if you have more values than 'Y' and 'N' in your actual table.
select marker
, MIN(val) as val
from TEMP_A
group by marker
having MIN(val) = MAX(val)
and MIN(val) = 'Y'
I like this most, and it definitely does not suffer from "Z" or any other value being in the data. ? ?
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);June 26, 2014 at 6:01 pm
I'd vote for Lynn's personally. His will return all the rows for Markers that have multiple rows all have Val 'Y'. This would become important if there are other columns to be returned.
For example
'A','Y',0.01
'A','Y',1.24
'B','Y',1.4
'B','N',5.47
June 27, 2014 at 7:27 am
mickyT (6/26/2014)
I'd vote for Lynn's personally. His will return all the rows for Markers that have multiple rows all have Val 'Y'. This would become important if there are other columns to be returned.For example
'A','Y',0.01
'A','Y',1.24
'B','Y',1.4
'B','N',5.47
This would be a change in the requirements. For this type of thing I would agree but that is not at all what was originally posted.
The upside is that we have come up with a number of ways to accomplish this. They all have merit and will work in certain situations. There is no "best" way for much of anything in sql server across the board. Everything must be evaluated with reference to the task at hand.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 30, 2014 at 1:25 pm
Thanks everyone. Sean's implementation fits the bill. I appreciate all the help.
<><
Livin' down on the cube farm. Left, left, then a right.
July 4, 2014 at 3:22 am
SELECTMarker
FROMdbo.TEMP_A
GROUP BYMarker
HAVINGSUM(CASE WHEN Val = 'Y' THEN 0 ELSE 1 END) = 0
N 56°04'39.16"
E 12°55'05.25"
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply