Re: selecting the record with the latest date in a group.

"Orlando Amador" <oamador_at_psgapr.jnj.com> wrote in message news:6e75aa9.0109061205.61b04dad_at_posting.google.com...
> On a table like this I&#8217;m trying to retrieve the last user to
> visit every site.
>
> Site last_visit User
> Site1 09/03/00 Steve [not selected]
> Site1 10/21/00 Roger [selected]
> Site2 10/15/00 Roger [not selected]
> Site2 11/06/00 Steve [selected]
> Site3 09/25/00 Scott [selected]
>
> A query like "select site, max(last_visit) from table group by site"
> will return the sites and the last visit date.
>
> I need to learn how to add, if it is possible, the user column to that
> last query.
>
> I tried "select site, user, max(last_visit) from table group by site,
> user" but now it returns all the rows.
>
> Any advice?
>
> Saludos,
> Orlando
>
> Orlando M. Amador
> Senior System Analyst
> Ortho Biologics LLC
> oamador_at_psgapr.jnj.com

Homework...
select site, user, last_visit
from table x
where last_visit =
(select max(last_visit
  from table y
  where site = x.site
   and user = x.user
)
Question: why do you have a column user, which is a reserved

Regards,

Sybrand Bakker, Senior Oracle DBA Received on Thu Sep 06 2001 - 16:36:36 CDT