Re: How to get values of more than two columns in a group by ?

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Fri, 26 Jul 2002 15:43:25 +1000
Message-ID: <Pc509.44160$Hj3.134054@newsfeeds.bigpond.com>

Hi Sam,

When you perform a GROUP BY, you are effectively summarising the data based these columns. Therefore it doesn't make sense for Oracle to display other columns as these could contain multiple values so which value should Oracle choose. Well it can't make that decision hence the errors you receive.

However, there are various ways to write a query that will return the data as you request. These techniques will require multiple passes of the same table. One way would be to use an inline view (as they seem topical at the moment).

Try something like:

SELECT a.last_name, bowie_column, max_date FROM arrival table a, (SELECT last_name, MAX(arrive_date) max_date

                                    FROM arrival table
                                    GROUP BY last_name) b

WHERE a.last_name = b.last_name;

You now have your previous columns and another column of interest.

A correlated subquery would be another way of doing the same sort of thing.

Hope this helps

Good Luck

Richard

"Sam Talebbeik" <sam777t_at_yahoo.com> wrote in message news:9ec3a1ef.0207251631.786ebe62_at_posting.google.com...
> Folks,
>
> I have a general question about GROUP BY functionality.
> assume that I have a table that keeps track of arrival
> dates of different guests. These guests arrive and leave
> several times. So each unique guest (unique last name) may have multiple
> arrival dates. If I write a query like:
>
> SELECT last_name , MAX(arrive_date)
> FROM arrival table
> GROUP BY last_name ;
>
> This query will print the latest arrival date of a specific
> guest. I know that this works.
>
> How do I get the values for the other columns in the returned rows ?
> If I put the other column names in the SELECT and the GROUP BY clauses, I
> do not get the desired result.
>
> Is there anyway to do this with the GROUP BY or any other equivalent
> SQL statement ?
>
> Regards,
> Sam
Received on Fri Jul 26 2002 - 00:43:25 CDT