Table.* notation does not work in a 'group by' query

Question

On an oracle database, the Table.* notation does not work inside a 'select..group by..' query.

This query with no * works :

select A.id from TABLE_A A INNER JOIN TABLE_B B on A.id=B.aid group by A.id

This one with a * does not :

select A.*  from TABLE_A A INNER JOIN TABLE_B B on A.id=B.aid group by A.id

The output is

00979. 00000 -  "not a GROUP BY expression"

Why does this query not work? Is there a simple workaround?

Answer 1

Everything you selecting except agregate functions (MIN, MAX, SUM, AVG, COUNT...) must be in Group by

Answer 2

Yes, there is a workaround.
Assuming that each id in A is unique, then you don't even need to use group by, just:

select * from A
where id in (
   select id from b
); 

If id are not unique in A table, then you can simulate MySql functionality with this query:

select * from A
where rowid in (
  select min( a.rowid ) 
  from a
  join b on a.id = b.id
  group by a.id
);

Here is a link to SQL Fiddle demo

Here is a link to MySql documentation where their extension to group by is explained: http://dev.mysql.com/doc/refman/5.1/en/group-by-extensions.html
Pay attention to this fragment:

You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Sorting of the result set occurs after values have been chosen, and ORDER BY does not affect which values within each group the server chooses.