EXPERT RESPONSE
Every once in a while, a question is submitted
to us here at Ask The Expert which looks simple on the surface,
but turns out to be tricky. My first reaction to this
question was to use a simple WHERE clause:
select nSeqID
, Name
, Status
from Invent
where Status = 'y'
However, this is wrong, because while it eliminates
the nSeqID=2 row where Status='n', it will not eliminate
the other rows for nSeqID=2.
The solution involves, as you suggested, grouping.
We need to form groups of rows, one group per nSeqID,
and make sure that the only Status within each group is 'y'.
This is done by counting the number of different statuses,
and then making sure that there's only one of them,
and that its lowest value is 'y'. This may sound weird,
but it works quite well, and there's no easier way to say it.
select nSeqID
, Name
, Status
from Invent
where nSeqID
in (
select nSeqID
from Invent
group
by nSeqID
having count(distinct Status) = 1
and min(Status) = 'y'
)
The subquery decides which nSeqIDs have only Status='y' rows.
Then the outer query returns all rows for those nSeqIDs.
Notice that you can't put the Status='y' condition into a
WHERE clause in the subquery, because that would return all nSeqIDs
which have a Status='y', even if they have a Status='n' too,
because the Status='n' rows wouldn't make it to the grouping step
in the subquery. Also, note that we must use an aggregate function
in the HAVING clause because syntax rules demand it, but we could use
either MIN or MAX, because both of them
will return the same result, since there's only one value in the group. For More Information
|
