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

Sashi schrieb:

htht,
Urs Metzger

Sashi schrieb:

htht,
Urs Metzger

Sashi schrieb:

htht,
Urs Metzger

Sashi schrieb:

htht,
Urs Metzger

On May 12, 10:55 am, Urs Metzger <u... (AT) ursmetzger (DOT) de> wrote:
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?

On May 12, 10:55 am, Urs Metzger <u... (AT) ursmetzger (DOT) de> wrote:
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?

On May 12, 10:55 am, Urs Metzger <u... (AT) ursmetzger (DOT) de> wrote:
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?

On May 12, 10:55 am, Urs Metzger <u... (AT) ursmetzger (DOT) de> wrote:
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?

Sashi schrieb:

select bldg, floor
from T
group by bldg, floor
having count(*) = 1

hth,
Urs Metzger