I have a table with multiple columns (SQL Server). Only two columns are relevant to the question however.
Column a = ID (a numeric value)
Column b = Status (String)
| ID | Status |
|---|---|
| 100 | Banana |
| 100 | Apple |
| 101 | Apple |
| 102 | Banana |
| 102 | Apple |
| 102 | Grape |
I want to return only the rows which contain a single value of ‘Apple’ in this case this would be row 3, ID 101.
Although the other ID’s also have a value of ‘Apple’ I’m not interested in these as they have other values as well.
I’ve tried, combinations of IF, HAVING, with Case expressions and partitions. I thought I had it a couple of times but sadly not.
Here’s one of my incorrect attempts:
SELECT
ID
, STATUS
FROM table name
GROUP BY ID, STATUS
HAVING COUNT(DISTINCT STATUS) = 1
AND MAX(STATUS) = 'APPLE'
And this:
SELECT
ID
, STATUS
, IF(SUM(CASE WHEN STATUS = 'Apple' THEN 1 ELSE 0 END)
OVER (PARTITION BY id) >0, 'TRUE', 'FALSE') AS Q_IDENTIFIER
FROM table_name
5
There are a couple of ways of peeling this apple.
CREATE TABLE #Data (ID INT, Status NVARCHAR(100))
INSERT INTO #Data
SELECT *
FROM
(
VALUES (100, N'Banana')
, (100, N'Apple')
, (101, N'Apple')
, (102, N'Banana')
, (102, N'Apple')
, (102, N'Grape')
) t (ID,Status)
Window aggregates:
SELECT *
FROM (
SELECT *
, COUNT(*) OVER(partition BY ID) AS totalCount
FROM #Data
) x
WHERE Status = 'Apple'
AND totalCount = 1
This counts all rows for every ID ahead of time, which allows you to then filter out the desired rows outside of the subquery
Exists:
SELECT *
FROM #Data d
WHERE Status = 'Apple'
AND NOT EXISTS(
SELECT 1
FROM #Data d2
WHERE d2.ID = d.ID
AND d2.Status <> d.Status
)
This just checks so there are no other rows in the same ID-group. This can have downsides of returning duplicates if you have more than one Apple available.
0
By correlated logic
select * from tdata t1 where 1 =
( select count(id) from tdata t2
where t2.id = t1.id and t1.status='Apple');