Re: build table from "logging" table, one row per user - oldest date
From: ddf <oratune_at_msn.com>
Date: Fri, 17 Sep 2010 06:01:27 -0700 (PDT)
Message-ID: <f8049b17-77bc-41a6-845f-a420303da753_at_v35g2000prn.googlegroups.com>
On Sep 16, 8:00 pm, okey <oldyor..._at_yahoo.com> wrote:
> 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.- Hide quoted text -
>
> - Show quoted text -
Date: Fri, 17 Sep 2010 06:01:27 -0700 (PDT)
Message-ID: <f8049b17-77bc-41a6-845f-a420303da753_at_v35g2000prn.googlegroups.com>
On Sep 16, 8:00 pm, okey <oldyor..._at_yahoo.com> wrote:
> 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.- Hide quoted text -
>
> - Show quoted text -
Who can say siince you haven't posted a create table statement and some sample data. We have no idea what your data looks like so how can we suggest an alternative?
David Fitzjarrell Received on Fri Sep 17 2010 - 08:01:27 CDT