I have a table Invent with the following sample data.
nSeqID Name Status 1 ABC y 1 BCD y 1 CEF y 2 GHI y 2 HIJ n 2 IJK y 3 JKL y 3 KLM y 3 LMN y
I need to show the result then group the results based on nSeqID and which the status should be 'y'. Required output is as follows:
nSeqID Name Status 1 ABC y 1 BCD y 1 CEF y 3 JKL y 3 KLM y 3 LMN y
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
- Dozens more answers to tough SQL questions from Rudy Limeback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, SQL Server, DB2, object-oriented and data warehousing gurus are waiting to answer your toughest questions.