getting last record(s) inserted into a table

hi,
i want to get the rows last inserted into a table, there is no
identifier/auto incr column to determine? is it possible to do this? i think
physical order the records inserted may help, but how could we get the
records in the reverse physical order they inserted?
thanksif you don't have the primary key for the table then you can have a column
in the table with 'timestamp' datat type. Then you can enlist the last
record by
select top 1 * from mytable order by timestamp_column desc
"Philip" <philipfairheight_@.hotmail.com> wrote in message
news:ej1tNkLmDHA.2000@.TK2MSFTNGP12.phx.gbl...
> hi,
> i want to get the rows last inserted into a table, there is no
> identifier/auto incr column to determine? is it possible to do this? i
think
> physical order the records inserted may help, but how could we get the
> records in the reverse physical order they inserted?
> thanks
>|||Actually, the previous suggestion will not work if you are doing any
updating... See example
drop table test
go
create table test (id int identity(1,1), ti timestamp, a varchar(100) not
null )
go
insert into test(a) values ('hi')
insert into test(a) values ('hi')
insert into test(a) values ('hi')
insert into test(a) values ('hi')
insert into test(a) values ('hi')
insert into test(a) values ('hi')
go
select * from test
go
update test set a = 'lo' where id = 3
go
select * from test
You'll notice that the timestamp column value for id = 3 is the largest...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Philip" <philipfairheight_@.hotmail.com> wrote in message
news:ej1tNkLmDHA.2000@.TK2MSFTNGP12.phx.gbl...
> hi,
> i want to get the rows last inserted into a table, there is no
> identifier/auto incr column to determine? is it possible to do this? i
think
> physical order the records inserted may help, but how could we get the
> records in the reverse physical order they inserted?
> thanks
>|||u r right . because the timestamp value is updated automatically whenever
the record is inserted or updated.
poor solution for poor table design. (how come there be a table without
identifer (PK) ?)
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:ugZ%23$fMmDHA.372@.TK2MSFTNGP11.phx.gbl...
> Actually, the previous suggestion will not work if you are doing any
> updating... See example
> drop table test
> go
> create table test (id int identity(1,1), ti timestamp, a varchar(100) not
> null )
> go
> insert into test(a) values ('hi')
> insert into test(a) values ('hi')
> insert into test(a) values ('hi')
> insert into test(a) values ('hi')
> insert into test(a) values ('hi')
> insert into test(a) values ('hi')
> go
> select * from test
> go
> update test set a = 'lo' where id = 3
> go
> select * from test
> You'll notice that the timestamp column value for id = 3 is the largest...
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "Philip" <philipfairheight_@.hotmail.com> wrote in message
> news:ej1tNkLmDHA.2000@.TK2MSFTNGP12.phx.gbl...
> > hi,
> > i want to get the rows last inserted into a table, there is no
> > identifier/auto incr column to determine? is it possible to do this? i
> think
> > physical order the records inserted may help, but how could we get the
> > records in the reverse physical order they inserted?
> > thanks
> >
> >
>|||You also have the option of including and update trigger
on the table to update a date/time field to the time of
the update. These operations can start to get a bit
expensive depending on how many updates you are doing. If
you are doing primarily inserts a default will work fine.
I do not know of any way to get this information with the
current internal storage structures.
Skippy|||anyway, shall we depend on the structure of the data, i mean sql server
returns always the records in the order they are inserted or this order
changes when pages optimized/compressed?
"I_AM_DON_AND_YOU?" <user@.domain.com> wrote in message
news:eqMtgmMmDHA.3688@.TK2MSFTNGP11.phx.gbl...
> u r right . because the timestamp value is updated automatically whenever
> the record is inserted or updated.
> poor solution for poor table design. (how come there be a table without
> identifer (PK) ?)
> "Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
> news:ugZ%23$fMmDHA.372@.TK2MSFTNGP11.phx.gbl...
> > Actually, the previous suggestion will not work if you are doing any
> > updating... See example
> >
> > drop table test
> > go
> > create table test (id int identity(1,1), ti timestamp, a varchar(100)
not
> > null )
> > go
> > insert into test(a) values ('hi')
> > insert into test(a) values ('hi')
> > insert into test(a) values ('hi')
> > insert into test(a) values ('hi')
> > insert into test(a) values ('hi')
> > insert into test(a) values ('hi')
> > go
> > select * from test
> > go
> > update test set a = 'lo' where id = 3
> > go
> > select * from test
> >
> > You'll notice that the timestamp column value for id = 3 is the
largest...
> >
> >
> > --
> > Wayne Snyder, MCDBA, SQL Server MVP
> > Computer Education Services Corporation (CESC), Charlotte, NC
> > www.computeredservices.com
> > (Please respond only to the newsgroups.)
> >
> > I support the Professional Association of SQL Server (PASS) and it's
> > community of SQL Server professionals.
> > www.sqlpass.org
> >
> >
> > "Philip" <philipfairheight_@.hotmail.com> wrote in message
> > news:ej1tNkLmDHA.2000@.TK2MSFTNGP12.phx.gbl...
> > > hi,
> > > i want to get the rows last inserted into a table, there is no
> > > identifier/auto incr column to determine? is it possible to do this? i
> > think
> > > physical order the records inserted may help, but how could we get the
> > > records in the reverse physical order they inserted?
> > > thanks
> > >
> > >
> >
> >
>|||> anyway, shall we depend on the structure of the data
No. The optimizer is free to process the query in any way it wants. You cannot rely on anything
(indexes, order of inserts etc). If you don't have ORDER BY, you get the rows in the order the
optimizer will find most efficient.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Philip" <philipfairheight_@.hotmail.com> wrote in message
news:eOlOPaSmDHA.744@.tk2msftngp13.phx.gbl...
> anyway, shall we depend on the structure of the data, i mean sql server
> returns always the records in the order they are inserted or this order
> changes when pages optimized/compressed?
> "I_AM_DON_AND_YOU?" <user@.domain.com> wrote in message
> news:eqMtgmMmDHA.3688@.TK2MSFTNGP11.phx.gbl...
> > u r right . because the timestamp value is updated automatically whenever
> > the record is inserted or updated.
> >
> > poor solution for poor table design. (how come there be a table without
> > identifer (PK) ?)
> >
> > "Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
> > news:ugZ%23$fMmDHA.372@.TK2MSFTNGP11.phx.gbl...
> > > Actually, the previous suggestion will not work if you are doing any
> > > updating... See example
> > >
> > > drop table test
> > > go
> > > create table test (id int identity(1,1), ti timestamp, a varchar(100)
> not
> > > null )
> > > go
> > > insert into test(a) values ('hi')
> > > insert into test(a) values ('hi')
> > > insert into test(a) values ('hi')
> > > insert into test(a) values ('hi')
> > > insert into test(a) values ('hi')
> > > insert into test(a) values ('hi')
> > > go
> > > select * from test
> > > go
> > > update test set a = 'lo' where id = 3
> > > go
> > > select * from test
> > >
> > > You'll notice that the timestamp column value for id = 3 is the
> largest...
> > >
> > >
> > > --
> > > Wayne Snyder, MCDBA, SQL Server MVP
> > > Computer Education Services Corporation (CESC), Charlotte, NC
> > > www.computeredservices.com
> > > (Please respond only to the newsgroups.)
> > >
> > > I support the Professional Association of SQL Server (PASS) and it's
> > > community of SQL Server professionals.
> > > www.sqlpass.org
> > >
> > >
> > > "Philip" <philipfairheight_@.hotmail.com> wrote in message
> > > news:ej1tNkLmDHA.2000@.TK2MSFTNGP12.phx.gbl...
> > > > hi,
> > > > i want to get the rows last inserted into a table, there is no
> > > > identifier/auto incr column to determine? is it possible to do this? i
> > > think
> > > > physical order the records inserted may help, but how could we get the
> > > > records in the reverse physical order they inserted?
> > > > thanks
> > > >
> > > >
> > >
> > >
> >
> >
>