Last row  
Author Message
AFN





PostPosted: Wed Apr 28 15:01:54 CDT 2004 Top

SQL Server Developer >> Last row

Hi -

I have a table with a composite index of id and datetime.
How can I select the last row of this group? In other
words the one with the most recent date with a given ID?
Also if I want to update this row how would the sql be?
Thanks in advance.

SQL Server271  
 
 
Tom





PostPosted: Wed Apr 28 15:01:54 CDT 2004 Top

SQL Server Developer >> Last row Try:

select
m.*
from
MyTable m
join
(
select
id
, max ([datetime]) as [datetime]
from
MyTable
group by
id
) as x on x.id = m.id
and x.[datetime] = m.[datetime]

The update depends on what you want to update.

--
Tom

---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql




Hi -

I have a table with a composite index of id and datetime.
How can I select the last row of this group? In other
words the one with the most recent date with a given ID?
Also if I want to update this row how would the sql be?
Thanks in advance.

 
 
Anith





PostPosted: Wed Apr 28 15:06:56 CDT 2004 Top

SQL Server Developer >> Last row >> In other words the one with the most recent date with a given ID? <<

SELECT *
FROM tbl
WHERE dtcol = ( SELECT MAX( t1.dtcol )
FROM tbl t1
WHERE t1.grpcol = tbl.grpcol );

>> Also if I want to update this row how would the sql be? <<

UPDATE tbl
SET ....
WHERE dtcol = ( SELECT MAX( t1.dtcol )
FROM tbl t1
WHERE t1.grpcol = tbl.grpcol );

--
Anith


 
 
anonymous





PostPosted: Wed Apr 28 16:21:09 CDT 2004 Top

SQL Server Developer >> Last row Thanks guys for the quick response - it worked :-)
>-----Original Message-----
>>> In other words the one with the most recent date with
a given ID? <<
>
>SELECT *
> FROM tbl
> WHERE dtcol = ( SELECT MAX( t1.dtcol )
> FROM tbl t1
> WHERE t1.grpcol = tbl.grpcol );
>
>>> Also if I want to update this row how would the sql
be? <<
>
>UPDATE tbl
> SET ....
> WHERE dtcol = ( SELECT MAX( t1.dtcol )
> FROM tbl t1
> WHERE t1.grpcol = tbl.grpcol );
>
>--
>Anith
>
>
>.
>