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

From: Sam Talebbeik <sam777t_at_yahoo.com>
Date: 25 Jul 2002 23:44:00 -0700
Message-ID: <9ec3a1ef.0207252243.e5649e1@posting.google.com>

I just want to share my information. It seems like the only way to do this is to use a query in the FROM clause.

SELECT a.first_name, b.last_name, b.max_d FROM arrivale_table a, (SELECT last_name, MAX(arrive_date) max_d

                        FROM arrival_table
                        GROUP BY last_name) b

WHERE (a.last_name= b.last_name) and (a.arrive_date = b.arrive_date) ;

I will be interested to hear if there are any other solutions to this problem.

    Regards,
    Sam

sam777t_at_yahoo.com (Sam Talebbeik) 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 - 01:44:00 CDT