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

From: Orlando Amador <oamador_at_psgapr.jnj.com>
Date: 7 Sep 2001 06:44:49 -0700
Message-ID: <6e75aa9.0109070544.7d6ae1da@posting.google.com>

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:<tpfrg4n8ltnqd6_at_news.demon.nl>...
> "Orlando Amador" <oamador_at_psgapr.jnj.com> wrote in message
> news:6e75aa9.0109061205.61b04dad_at_posting.google.com...
> > On a table like this I’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

Sorry that the request was no challenging enough. :-) True to your statement, I only took the intro to SQL training some year ago and been getting by with that since I don't use it very often.

Thanks for the response, now the result is what I was expecting. I tried doing this with grouping, but did not work. I see now that I was going the wrong way about it.

Just so that I learn something from this, the outer query qualifies which records to select. Then use the columns in the outer query that would form the group of records to filter the results on the inner query and return the oldest date of that inner result set.

So based on that understanding (or maybe misunderstanding) and that the groups are base on the site only. Should I take out the user column from the where condition on the inner query? That would select the last date of a particular site. Correct?

If the table had another column named service_type and I would qualify the outer query with a where clase like "where service_type = 1", would that where clause also have to be included in the inner query?

For example:

select site, user_name, last_visit, service_type from table x
where last_visit =

( select max(last_visit)

     from table y
     where y.site = x.site
     and y.service_type = 1

)
and service_type = 1;

PS. You are correct about the columns names, I just made the table on the message as an example.

Saludos,
Orlando

Orlando M. Amador
Senior System Analyst
Ortho Biologics LLC
oamador_at_psgapr.jnj.com Received on Fri Sep 07 2001 - 08:44:49 CDT