"Jeff" wrote:

>     I need some ideas on this query.
>
>    I have a table with entries similar to the following with columns name,
> id, and timestamp.
>
> kmyoung    345    2005-08-22 07:29:00.000
> kmyoung    345    2005-08-29 07:29:15.000
> mphillips    360    2005-08-27 14:48:18.000
> rbeheler    360    2005-08-22 09:29:11.000
> rbeheler    360    2005-08-24 09:28:19.000
> rbeheler    360    2005-08-29 09:27:54.000
>
>   I need a resultant set that gives me the records with the most recent
> timestamp for each ID as listed below. 
>
> kmyoung    345    2005-08-29 07:29:15.000
> rbeheler    360    2005-08-29 09:27:54.000
>
>    Thanks for the help.
>

"Alejandro Mesa" wrote:

> Try,
>
> select
>    *
> from
>    t1 as a
> where
>    c3 = (select max(b.c3) from t1 as b where b.[id] = a.[id])
> go
>
>
> AMB
>
> "Jeff" wrote:
>
> >     I need some ideas on this query.
> >
> >    I have a table with entries similar to the following with columns name,
> > id, and timestamp.
> >
> > kmyoung    345    2005-08-22 07:29:00.000
> > kmyoung    345    2005-08-29 07:29:15.000
> > mphillips    360    2005-08-27 14:48:18.000
> > rbeheler    360    2005-08-22 09:29:11.000
> > rbeheler    360    2005-08-24 09:28:19.000
> > rbeheler    360    2005-08-29 09:27:54.000
> >
> >   I need a resultant set that gives me the records with the most recent
> > timestamp for each ID as listed below. 
> >
> > kmyoung    345    2005-08-29 07:29:15.000
> > rbeheler    360    2005-08-29 09:27:54.000
> >
> >    Thanks for the help.
> >

"Jeff" wrote:

> It worked great as long as t1 was an actual table.  But in actuality t1 is a
> union of two tables.  When I substitute (select * from t1 union select * from
> t2) as t1, it no longer works.  Would it be possible to rewrite this with a
> subquery instead of t1?
>
> "Alejandro Mesa" wrote:
>
> > Try,
> >
> > select
> >    *
> > from
> >    t1 as a
> > where
> >    c3 = (select max(b.c3) from t1 as b where b.[id] = a.[id])
> > go
> >
> >
> > AMB
> >
> > "Jeff" wrote:
> >
> > >     I need some ideas on this query.
> > >
> > >    I have a table with entries similar to the following with columns name,
> > > id, and timestamp.
> > >
> > > kmyoung    345    2005-08-22 07:29:00.000
> > > kmyoung    345    2005-08-29 07:29:15.000
> > > mphillips    360    2005-08-27 14:48:18.000
> > > rbeheler    360    2005-08-22 09:29:11.000
> > > rbeheler    360    2005-08-24 09:28:19.000
> > > rbeheler    360    2005-08-29 09:27:54.000
> > >
> > >   I need a resultant set that gives me the records with the most recent
> > > timestamp for each ID as listed below. 
> > >
> > > kmyoung    345    2005-08-29 07:29:15.000
> > > rbeheler    360    2005-08-29 09:27:54.000
> > >
> > >    Thanks for the help.
> > >

>     I need some ideas on this query.
>
>    I have a table with entries similar to the following with columns name,
> id, and timestamp.
>
> kmyoung    345    2005-08-22 07:29:00.000
> kmyoung    345    2005-08-29 07:29:15.000
> mphillips    360    2005-08-27 14:48:18.000
> rbeheler    360    2005-08-22 09:29:11.000
> rbeheler    360    2005-08-24 09:28:19.000
> rbeheler    360    2005-08-29 09:27:54.000
>
>   I need a resultant set that gives me the records with the most recent
> timestamp for each ID as listed below. 
>
> kmyoung    345    2005-08-29 07:29:15.000
> rbeheler    360    2005-08-29 09:27:54.000
>
>    Thanks for the help.
>

"Trey Walpole" wrote:

> select name, id, max(timestamp) as timestamp
> from thetable
>   group by name, id
> having count(*)>1 -- if you need just the ones that have dupes, add this
>    line
>
>
> Jeff wrote:
> >     I need some ideas on this query.
> >
> >    I have a table with entries similar to the following with columns name,
> > id, and timestamp.
> >
> > kmyoung    345    2005-08-22 07:29:00.000
> > kmyoung    345    2005-08-29 07:29:15.000
> > mphillips    360    2005-08-27 14:48:18.000
> > rbeheler    360    2005-08-22 09:29:11.000
> > rbeheler    360    2005-08-24 09:28:19.000
> > rbeheler    360    2005-08-29 09:27:54.000
> >
> >   I need a resultant set that gives me the records with the most recent
> > timestamp for each ID as listed below. 
> >
> > kmyoung    345    2005-08-29 07:29:15.000
> > rbeheler    360    2005-08-29 09:27:54.000
> >
> >    Thanks for the help.
> >
>

> Very close, but I ended up with this resultant set instead.
>
> kmyoung    345    2005-08-29 07:29:15.000
> mphillips    360    2005-08-27 14:48:18.000
> rbeheler    360    2005-08-29 09:27:54.000
>
> I ended up with two entries for id 360.
>
> "Trey Walpole" wrote:
>
>
>>select name, id, max(timestamp) as timestamp
>>from thetable
>>  group by name, id
>>having count(*)>1 -- if you need just the ones that have dupes, add this
>>   line
>>
>>
>>Jeff wrote:
>>
>>>    I need some ideas on this query.
>>>
>>>   I have a table with entries similar to the following with columns name,
>>>id, and timestamp.
>>>
>>>kmyoung    345    2005-08-22 07:29:00.000
>>>kmyoung    345    2005-08-29 07:29:15.000
>>>mphillips    360    2005-08-27 14:48:18.000
>>>rbeheler    360    2005-08-22 09:29:11.000
>>>rbeheler    360    2005-08-24 09:28:19.000
>>>rbeheler    360    2005-08-29 09:27:54.000
>>>
>>>  I need a resultant set that gives me the records with the most recent
>>>timestamp for each ID as listed below. 
>>>
>>>kmyoung    345    2005-08-29 07:29:15.000
>>>rbeheler    360    2005-08-29 09:27:54.000
>>>
>>>   Thanks for the help.
>>>
>>