Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

select with join and group by

Posted on 2007-11-15
5
Medium Priority
?
3,301 Views
Last Modified: 2013-12-07
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
Comment
Question by:flscooter
  • 2
  • 2
5 Comments
 
LVL 12

Accepted Solution

by:
jwahl earned 252 total points
ID: 20289882
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
 

Author Comment

by:flscooter
ID: 20289976
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
 
LVL 18

Assisted Solution

by:Jinesh Kamdar
Jinesh Kamdar earned 248 total points
ID: 20290017
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
 
LVL 12

Expert Comment

by:jwahl
ID: 20290033
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
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 20290087
@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

Industry Leaders: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to recover a database from a user managed backup
Suggested Courses

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question