Groups having only one value in a column

Groups having only one value in a column

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

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchOracle.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchOracle.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

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


This was first published in July 2004

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top