Retrieving only newest rows 
Author Message
 Retrieving only newest rows
I have a table with some rows duplicated except for the timestamp column
that is adjusted whenever any information is altered (all the other columns,
including.the ID, are duplicated in the inserted row).  Not my design, but
one I cannot alter.

I'd like to use a TSQL statement to return all rows in the table but only
one per ID - using the row with the latest timestamp if the count of the ID
is greater than 1.  Help on syntax??

Jeff



Sat, 22 May 2004 04:42:21 GMT
 Retrieving only newest rows

Jeff,

  How about

select * from Jeff T
where timestampCol >= all (
  select timestampCol from Jeff
  where idCol = T.idCol)

In other words, select all the rows from
the table with the latest timestamp column
for their ID.

Steve Kass
Drew University

Quote:

> I have a table with some rows duplicated except for the timestamp column
> that is adjusted whenever any information is altered (all the other columns,
> including.the ID, are duplicated in the inserted row).  Not my design, but
> one I cannot alter.

> I'd like to use a TSQL statement to return all rows in the table but only
> one per ID - using the row with the latest timestamp if the count of the ID
> is greater than 1.  Help on syntax??

> Jeff



Sat, 22 May 2004 04:46:17 GMT
 Retrieving only newest rows
jeff,

select [id],max(timestamp)
from tbname
group by [id]

-oj


Quote:
> I have a table with some rows duplicated except for the timestamp column
> that is adjusted whenever any information is altered (all the other
columns,
> including.the ID, are duplicated in the inserted row).  Not my design, but
> one I cannot alter.

> I'd like to use a TSQL statement to return all rows in the table but only
> one per ID - using the row with the latest timestamp if the count of the
ID
> is greater than 1.  Help on syntax??

> Jeff



Sat, 22 May 2004 04:49:11 GMT
 Retrieving only newest rows
hehehhe...i see you switch over to all/any stuff...

-oj


Quote:
> Jeff,

>   How about

> select * from Jeff T
> where timestampCol >= all (
>   select timestampCol from Jeff
>   where idCol = T.idCol)

> In other words, select all the rows from
> the table with the latest timestamp column
> for their ID.

> Steve Kass
> Drew University


> > I have a table with some rows duplicated except for the timestamp column
> > that is adjusted whenever any information is altered (all the other
columns,
> > including.the ID, are duplicated in the inserted row).  Not my design,
but
> > one I cannot alter.

> > I'd like to use a TSQL statement to return all rows in the table but
only
> > one per ID - using the row with the latest timestamp if the count of the
ID
> > is greater than 1.  Help on syntax??

> > Jeff



Sat, 22 May 2004 04:52:02 GMT
 Retrieving only newest rows
steve,

did you performance test to see if all approach is faster than group by?

-oj


Quote:
> Jeff,

>   How about

> select * from Jeff T
> where timestampCol >= all (
>   select timestampCol from Jeff
>   where idCol = T.idCol)

> In other words, select all the rows from
> the table with the latest timestamp column
> for their ID.

> Steve Kass
> Drew University


> > I have a table with some rows duplicated except for the timestamp column
> > that is adjusted whenever any information is altered (all the other
columns,
> > including.the ID, are duplicated in the inserted row).  Not my design,
but
> > one I cannot alter.

> > I'd like to use a TSQL statement to return all rows in the table but
only
> > one per ID - using the row with the latest timestamp if the count of the
ID
> > is greater than 1.  Help on syntax??

> > Jeff



Sat, 22 May 2004 04:57:25 GMT
 Retrieving only newest rows
select t1.*
  from tbl as t1
 where t1.timestampcol = ( select max( t2.timestampcol )
                             from tbl as t2
                            where t2.id = t1.id
                            group by t2.id
                           having count(*) > 1 )
--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
( Please reply only to newsgroup. )


Sat, 22 May 2004 05:13:36 GMT
 Retrieving only newest rows
No, did you?  ;)
Quote:

> steve,

> did you performance test to see if all approach is faster than group by?

> -oj



> > Jeff,

> >   How about

> > select * from Jeff T
> > where timestampCol >= all (
> >   select timestampCol from Jeff
> >   where idCol = T.idCol)

> > In other words, select all the rows from
> > the table with the latest timestamp column
> > for their ID.

> > Steve Kass
> > Drew University


> > > I have a table with some rows duplicated except for the timestamp column
> > > that is adjusted whenever any information is altered (all the other
> columns,
> > > including.the ID, are duplicated in the inserted row).  Not my design,
> but
> > > one I cannot alter.

> > > I'd like to use a TSQL statement to return all rows in the table but
> only
> > > one per ID - using the row with the latest timestamp if the count of the
> ID
> > > is greater than 1.  Help on syntax??

> > > Jeff



Sat, 22 May 2004 07:58:27 GMT
 Retrieving only newest rows
no... i think group by is faster than subquery.

-oj


Quote:
> No, did you?  ;)


> > steve,

> > did you performance test to see if all approach is faster than group by?

> > -oj



> > > Jeff,

> > >   How about

> > > select * from Jeff T
> > > where timestampCol >= all (
> > >   select timestampCol from Jeff
> > >   where idCol = T.idCol)

> > > In other words, select all the rows from
> > > the table with the latest timestamp column
> > > for their ID.

> > > Steve Kass
> > > Drew University


> > > > I have a table with some rows duplicated except for the timestamp
column
> > > > that is adjusted whenever any information is altered (all the other
> > columns,
> > > > including.the ID, are duplicated in the inserted row).  Not my
design,
> > but
> > > > one I cannot alter.

> > > > I'd like to use a TSQL statement to return all rows in the table but
> > only
> > > > one per ID - using the row with the latest timestamp if the count of
the
> > ID
> > > > is greater than 1.  Help on syntax??

> > > > Jeff



Sat, 22 May 2004 08:02:09 GMT
 Retrieving only newest rows
It looks like you're right, for just about any
kinds of indexes.  :(

Steve

Quote:

> no... i think group by is faster than subquery.

> -oj



> > No, did you?  ;)


> > > steve,

> > > did you performance test to see if all approach is faster than group by?

> > > -oj



> > > > Jeff,

> > > >   How about

> > > > select * from Jeff T
> > > > where timestampCol >= all (
> > > >   select timestampCol from Jeff
> > > >   where idCol = T.idCol)

> > > > In other words, select all the rows from
> > > > the table with the latest timestamp column
> > > > for their ID.

> > > > Steve Kass
> > > > Drew University


> > > > > I have a table with some rows duplicated except for the timestamp
> column
> > > > > that is adjusted whenever any information is altered (all the other
> > > columns,
> > > > > including.the ID, are duplicated in the inserted row).  Not my
> design,
> > > but
> > > > > one I cannot alter.

> > > > > I'd like to use a TSQL statement to return all rows in the table but
> > > only
> > > > > one per ID - using the row with the latest timestamp if the count of
> the
> > > ID
> > > > > is greater than 1.  Help on syntax??

> > > > > Jeff



Sat, 22 May 2004 08:12:07 GMT
 Retrieving only newest rows
hehehe...1 scan/seek vs 2.

-oj


Quote:
> It looks like you're right, for just about any
> kinds of indexes.  :(

> Steve


> > no... i think group by is faster than subquery.

> > -oj



> > > No, did you?  ;)


> > > > steve,

> > > > did you performance test to see if all approach is faster than group
by?

> > > > -oj



> > > > > Jeff,

> > > > >   How about

> > > > > select * from Jeff T
> > > > > where timestampCol >= all (
> > > > >   select timestampCol from Jeff
> > > > >   where idCol = T.idCol)

> > > > > In other words, select all the rows from
> > > > > the table with the latest timestamp column
> > > > > for their ID.

> > > > > Steve Kass
> > > > > Drew University


> > > > > > I have a table with some rows duplicated except for the
timestamp
> > column
> > > > > > that is adjusted whenever any information is altered (all the
other
> > > > columns,
> > > > > > including.the ID, are duplicated in the inserted row).  Not my
> > design,
> > > > but
> > > > > > one I cannot alter.

> > > > > > I'd like to use a TSQL statement to return all rows in the table
but
> > > > only
> > > > > > one per ID - using the row with the latest timestamp if the
count of
> > the
> > > > ID
> > > > > > is greater than 1.  Help on syntax??

> > > > > > Jeff



Sat, 22 May 2004 08:21:02 GMT
 
 [ 10 post ] 

 Relevant Pages 

1. How to retrieve the serial value for a new inserted row

2. Retrieving correct SERIAL number of new row

3. Retrieving a row by Row Number

4. New row, new ID, keep it open?

5. Adding new column to table with default for new rows

6. HowTo: Retrieve row results in order represented by the xml string

7. Retrieving first 20 or 30 rows of Table

8. Retrieving rows with TEXT columns.

9. Retrieving Row in a Datawarehouse

10. Row changed between retrieve and update

11. Retrieving 150 rows at a time

12. Retrieving the last row inserted in a table


 
Powered by phpBB® Forum Software