• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3302
  • Last Modified:

select with join and group by

Hi Experts,

I am using Oracle PL/SQL Version 9I.  I am trying to join 2 tables and select one other field.  The problem is that the table values I am joining on aren't unique and the other value I am bringing into the query is unique to the values I am joining on, but since they aren't unique, I get multiple iterations of combinations.  For example:
Field A         Field B        Fld_Value
100                100          S12345
100                100          S12345
100                100          S12345
Since the values in this result set are always the same, I tried to get one Fld_Value.  My original query is this:
select max(b.fld_value), a.fieldA, b.fieldB from foo.table_A  a, fi.table_B b             
where a.fieldA = b.fieldB.  
I got the error not a single-group group function.  No matter where I add the group by to ( for instance ):
select max(b.fld_value), a.fieldA, b.fieldB from foo.table_A  a, fi.table_B b       
group by b.field_value      
where a.fieldA = b.fieldB.  
I get some error saying the SQL command was not properly ended.  I am sure this is very simple, but I am not certain how to get this to work.  Thanks so much.

Scott
      
0
flscooter
Asked:
flscooter
  • 2
  • 2
2 Solutions
 
jwahlCommented:
place GROUP BY at the end:

SELECT   MAX (b.fld_value)
        ,a.fielda
        ,b.fieldb
FROM     foo.table_a a
        ,fi.table_b b
WHERE    a.fielda = b.fieldb
GROUP BY b.field_value;

but in your case a simple DISTINCT will do the same:



SELECT   DISTINCT b.fld_value
        ,a.fielda
        ,b.fieldb
FROM     foo.table_a a
        ,fi.table_b b
WHERE    a.fielda = b.fieldb;

Open in new window

0
 
flscooterAuthor Commented:
jwahl,
Thanks so much for getting back to me promptly.  I tried putting the group by at the end and I just tried the distinct clause, but I got the same result.  The word "where" is highlighted and I get an ORA-00933: SQL command not properly ended error.  I have no idea what I am being told.  Thanks.

Scott
0
 
Jinesh KamdarCommented:
The GROUP BY didnt have the right field name. But the SELECT with the DISTINCT should have worked. Try this.
SELECT   MAX (b.fld_value)
        ,a.fielda
        ,b.fieldb
FROM     foo.table_a a
        ,fi.table_b b
WHERE    a.fielda = b.fieldb
GROUP BY b.fld_value;

Open in new window

0
 
jwahlCommented:
sorry, should be
SELECT   MAX (b.fld_value)
        ,a.fielda
        ,b.fieldb
FROM     foo.table_a a
        ,fi.table_b b
WHERE    a.fielda = b.fieldb
GROUP BY a.fielda
        ,b.fieldb;

Open in new window

0
 
Jinesh KamdarCommented:
@jwahl: Good catch :)

@flscooter: I believe ur having a comma right before the FROM word 'coz even with the incorrect query, the error should have been "column not a part of GROUP BY" instead of what u mentioned. jwahl's query above should work just fine.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now