Re: build table from "logging" table, one row per user - oldest date
From: okey <oldyork90_at_yahoo.com>
Date: Thu, 16 Sep 2010 17:00:20 -0700 (PDT)
Message-ID: <3f1470a1-ee93-4923-97c6-a7d810be45b6_at_i17g2000vbq.googlegroups.com>
On Sep 16, 6:37 pm, okey <oldyor..._at_yahoo.com> wrote:
> I have a “logging” table. It records the user, login time, and other
> things.
>
> I want to create table within a statement that returns one row for
> each user. This row is the row containing the user’s oldest date
>
> Select blah blah from table x, (select user, date, other, info from
> logging where date is max) oldrows
> where x.date < oldrows.date
> and x.user = oldrows.user
>
> It’s building the table here that looks very difficult. This would be
> easy enough to do in pl/sql, but I rather do it this way, if it can
> even be done. This kind of thing is going to come up a lot.
>
> Thank you
Date: Thu, 16 Sep 2010 17:00:20 -0700 (PDT)
Message-ID: <3f1470a1-ee93-4923-97c6-a7d810be45b6_at_i17g2000vbq.googlegroups.com>
On Sep 16, 6:37 pm, okey <oldyor..._at_yahoo.com> wrote:
> I have a “logging” table. It records the user, login time, and other
> things.
>
> I want to create table within a statement that returns one row for
> each user. This row is the row containing the user’s oldest date
>
> Select blah blah from table x, (select user, date, other, info from
> logging where date is max) oldrows
> where x.date < oldrows.date
> and x.user = oldrows.user
>
> It’s building the table here that looks very difficult. This would be
> easy enough to do in pl/sql, but I rather do it this way, if it can
> even be done. This kind of thing is going to come up a lot.
>
> Thank you
I thought this might be a way, another subselect to find max date. As in:
select blah, blah from logtable t, (select distinct user, max(date) group by user) mx where mx.user = t.user ...
I'd have to assume the distinct user date was indeed unique. Not there yet unless I create yet another subselect to select that max date record and group it with a max unique key - any one would do.
You'd think there was something better. Received on Thu Sep 16 2010 - 19:00:20 CDT