 | |
Limit query output to a single record based on combination of fields
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#11
 
| |||
| |||
|
Re: Limit query output to a single record based on combination offields -
05-12-2008
, 10:36 AM
|
On May 12, 10:55 am, Urs Metzger <u... (AT) ursmetzger (DOT) 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
#12
| |||
| |||
|
|
On May 12, 10:55 am, Urs Metzger <u... (AT) ursmetzger (DOT) 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
#13
| |||
| |||
|
|
On May 12, 10:55 am, Urs Metzger <u... (AT) ursmetzger (DOT) 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
#14
| |||
| |||
|
|
On May 12, 10:55 am, Urs Metzger <u... (AT) ursmetzger (DOT) 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? You need to group by (at least) all columns which you |
the select list - you already know it to be 1.
WHERE is perfectly legal, but only before the GROUP BY
clause.
select bldg, floor
from T
where type like '%Switch'
group by bldg, floor
having count(*) = 1
hth,
Urs Metzger
#15
| |||
| |||
|
|
On May 12, 10:55 am, Urs Metzger <u... (AT) ursmetzger (DOT) 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? You need to group by (at least) all columns which you |
the select list - you already know it to be 1.
WHERE is perfectly legal, but only before the GROUP BY
clause.
select bldg, floor
from T
where type like '%Switch'
group by bldg, floor
having count(*) = 1
hth,
Urs Metzger
#16
| |||
| |||
|
|
On May 12, 10:55 am, Urs Metzger <u... (AT) ursmetzger (DOT) 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? You need to group by (at least) all columns which you |
the select list - you already know it to be 1.
WHERE is perfectly legal, but only before the GROUP BY
clause.
select bldg, floor
from T
where type like '%Switch'
group by bldg, floor
having count(*) = 1
hth,
Urs Metzger
#17
| |||
| |||
|
|
On May 12, 10:55 am, Urs Metzger <u... (AT) ursmetzger (DOT) 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? You need to group by (at least) all columns which you |
the select list - you already know it to be 1.
WHERE is perfectly legal, but only before the GROUP BY
clause.
select bldg, floor
from T
where type like '%Switch'
group by bldg, floor
having count(*) = 1
hth,
Urs Metzger
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.