Q
Problem solve Get help with specific problems with your technologies, process and projects.

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

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 last published in July 2004

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close