Re: Limit query output to a single record based on combination of fields

From: Sashi <smalladi_at_gmail.com>
Date: Mon, 12 May 2008 08:13:34 -0700 (PDT)
Message-ID: <eafbafa2-d064-44aa-8255-47b1b0501bd0@k13g2000hse.googlegroups.com>


On May 12, 10:55 am, Urs Metzger <u..._at_ursmetzger.de> wrote:
> Sashi schrieb:
>
> > Hi all,
>
> > I have a query as follows:
>
> > Select building, floor from T1
>
> > I would like to limit the output where the building, floor combination
> > is present only once in the output.
>
> > For example, if the output is
>
> > blgdA, 2
> > bldgA, 3
> > bldgA, 3
> > bldgB, 1
> > bldgB, 2
> > bldgB, 2
>
> > I would like to filter it so that the output is only
> > blgdA, 2
> > bldgB, 1
>
> > I'm not sure about how to do this.
>
> > Can someone help out?
> > Thanks,
> > Sashi
>
> Search the docs for GROUP BY and HAVING.
>
> htht,
> Urs Metzger

Thank you.
So I modified the query thus:
select bldg, count(Floor)
from T
group by bldg
having count(Floor) =1

However, when I try to include the floor number select lbcode_key, count(floor ), floor it fails: ORA-00979: not a GROUP BY expression  I want to be able to see the floor number. Also there is another field "type" and I need to filter it by this field as well, to include the regexp '%Switch'.

So my original query should have been:
Select building, floor from T1 where type like '%Switch'

However, the where clause is disallowed when using group by and the having clause doesn't like this either.

How do I do this? Received on Mon May 12 2008 - 10:13:34 CDT

Original text of this message