Re: Limit query output to a single record based on combination of fields
From: Urs Metzger <urs_at_ursmetzger.de>
Date: Mon, 12 May 2008 17:36:01 +0200
Message-ID: <g09o3v$ohn$1@online.de>
>
> 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?
OK:
Date: Mon, 12 May 2008 17:36:01 +0200
Message-ID: <g09o3v$ohn$1@online.de>
Sashi schrieb:
> 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?
OK:
select bldg, floor
from T
group by bldg, floor
having count(*) = 1
hth,
Urs Metzger
Received on Mon May 12 2008 - 10:36:01 CDT