IDENTITY or timestamp ?

[uselessninja]

hi everyone they said in relational database there is no "last" concept..
and the only way to retrieve the last data entered in database is by IDENTITY and sone says TIMESTAMP...

which one i would prefer to use?

[sv3tli0]

the best is with id (int, 11, auto increment, unsigned, primary key)
you can order it like you want it and you can manipulate it easy with maths calculations because its int

I prefer to use id even if there is timestamp field .. of course it depends on your needs.

[uselessninja]

the best is with id (int, 11, auto increment, unsigned, primary key)
you can order it like you want it and you can manipulate it easy with maths calculations because its int

I prefer to use id even if there is timestamp field .. of course it depends on your needs.

uhh ic... still not get it ^^

[pritaeas]

You choose whether you add an IDENTITY column, or a timestamp column to your table structure. My personal preference is to always use an IDENTITY column. Show your current table structure, and we will advise.

[sv3tli0]

let me describe it this way.
In your table you can set 2 fields ID or TIME
If you set id your table will create auto a number and it will auto increasing it by 1 for each new row.
This way you will have a number for each row. (first row will be id=1, next will be id=2, then id=3...) than you can order your table as you want it by this ID and to get it in order you need it (at your case in back order).
Its auto increment and you will be sure that the biggest number is the newest added row.

The second way is to create a field with current time .
With it you will have in exact what time have been added this row. To get the lates you will select order by time and it will order the rows by time added giving you the newest.
Its good option but you will be manipulating datetime , while at ID you will manipulate just normal numbers.

[adam_k]

I've seen good and bad implementations of both solutions.

How many records are we talking about? Is there a need to insert records before the "last" (either retrieve something from archive or a delayed insert from a mobile client) and how often?
There are a number of parameters for going one way or the other, so can you describe what you are building?

[uselessninja]

guyss.. i need to retrieve the last data entered in the database (MSSQL) in a specific date...let say i will retrieve the last data entered in the database of 9/27/2011 ..and i dunno hwo to do that ..im new to both PHP and MSSQL

i have a code but i think its not the appropriate one:

$sql="SELECT columname,columnname,columnname FROM generators WHERE ID=(select max(columnname) from generators)"; <<< it only retrieve the biggest row ID

thats why i ask about between the two

sorry mates .. iam totally newbie of MSSQL

[pritaeas]

With only your query it is very hard to determine what you have and what you want. Please post your table structure, some example data, and your desired output.

[adam_k]

guyss.. i need to retrieve the last data entered in the database (MSSQL) in a specific date...let say i will retrieve the last data entered in the database of 9/27/2011 ..and i dunno hwo to do that ..im new to both PHP and MSSQL

i have a code but i think its not the appropriate one:

$sql="SELECT columname,columnname,columnname FROM generators WHERE ID=(select max(columnname) from generators)"; <<< it only retrieve the biggest row ID

thats why i ask about between the two

sorry mates .. iam totally newbie of MSSQL

I'll have to agree with pritaeas, but based on the last data entered in a specific date, you can either

declare @specific_date = '2011-09-01'
select column1,column2 from table where record_create_date < dateadd(day,1, @specific_date)
order by record_create_date desc

or

declare @specific_date = '2011-09-01'
select column1, column2 from table where ID = (select max(ID) from table where record_create_date = @specific_date)

PS: The dateadd and the < exist in the first query to include records created in 23:59:59:999. If you don't like the dateadd you have to set the time in your date to 23:59:59:999 and select for smaller or equal to it.

[Netcode]

since you want to get the data by a particular date, then time-stamp answers your question and you can achieve that with Adam_K has just provided except you want something more detailed and then you would have to provide more details too for better understanding

[BitBlt]

Be cautious about the whole "Timestamp" thing. To begin with, the "TIMESTAMP" datatype has nothing to do with date or time. It is simply a binary stamp that has NO ACTUAL DATA SIGNIFICANCE other than uniqueness in THAT DATABASE.

Here's a little science experiment you can do to demonstrate how TIMESTAMP datatypes behave. First create a couple of tables that contain an identity column, a varchar column, and a timestamp column. Here's the "create" statement:

CREATE TABLE dbo.myTableWithIdentityAndTimestamp
(
myId        int IDENTITY(1,1) NOT NULL,
myVarchar   varchar(50) NULL,
myTimestamp timestamp NULL
)
CREATE TABLE dbo.myTableWithIdentityAndTimestamp2
(
myId        int IDENTITY(1,1) NOT NULL,
myVarchar   varchar(50) NULL,
myTimestamp timestamp NULL
)

Then, create a couple of insert statements to insert 3 rows into each. Run the insert statements together, then run them again. You should wind up with 6 rows in each table. Here are some samples:

insert into myTableWithIdentityAndTimestamp (myVarchar) values ('ABCDE'), ('FGHIJ'), ('KLMNO')
insert into myTableWithIdentityAndTimestamp2 (myVarchar) values ('ABCDE'), ('FGHIJ'), ('KLMNO')

Now, do a select from each table and compare.

select * from myTableWithIdentityAndTimestamp 
select * from myTableWithIdentityAndTimestamp2

You will notice that the identity columns have been automatically populated in ascending sequence in each table, as you would expect. You will also notice that the TIMESTAMP column was automatically populated, in ascending sequence, in the order in which the rows were inserted, REGARDLESS of which table received the inserted rows.

Now, update one row of each table. Here's some code:

update a set myVarchar = '123' from myTableWithIdentityAndTimestamp a where myId = 1
update a set myVarchar = '123' from myTableWithIdentityAndTimestamp2 a where myId = 2

Now, run the select statements again. Note that in the updated rows, TIMESTAMP has been updated. As long as you don't care WHEN the data has been updated, the timestamp could be used to see what was the last time anything in the entire row has been updated.

In conclusion, the TIMESTAMP column should not be used for date/time comparisons, and probably can't be relied upon for sequencing at all. It only guarantees a unique value within a database. Not only that, but it is a deprecated datatype that should be avoided. Look in MSSQL Books-Online at "rowversion" (the datatype that is replacing timestamp).

Hope this (long-winded) explanation is of some use. Good luck!

[uselessninja]

Be cautious about the whole "Timestamp" thing. To begin with, the "TIMESTAMP" datatype has nothing to do with date or time. It is simply a binary stamp that has NO ACTUAL DATA SIGNIFICANCE other than uniqueness in THAT DATABASE.

Here's a little science experiment you can do to demonstrate how TIMESTAMP datatypes behave. First create a couple of tables that contain an identity column, a varchar column, and a timestamp column. Here's the "create" statement:

CREATE TABLE dbo.myTableWithIdentityAndTimestamp
(
myId        int IDENTITY(1,1) NOT NULL,
myVarchar   varchar(50) NULL,
myTimestamp timestamp NULL
)
CREATE TABLE dbo.myTableWithIdentityAndTimestamp2
(
myId        int IDENTITY(1,1) NOT NULL,
myVarchar   varchar(50) NULL,
myTimestamp timestamp NULL
)

Then, create a couple of insert statements to insert 3 rows into each. Run the insert statements together, then run them again. You should wind up with 6 rows in each table. Here are some samples:

insert into myTableWithIdentityAndTimestamp (myVarchar) values ('ABCDE'), ('FGHIJ'), ('KLMNO')
insert into myTableWithIdentityAndTimestamp2 (myVarchar) values ('ABCDE'), ('FGHIJ'), ('KLMNO')

Now, do a select from each table and compare.

select * from myTableWithIdentityAndTimestamp 
select * from myTableWithIdentityAndTimestamp2

You will notice that the identity columns have been automatically populated in ascending sequence in each table, as you would expect. You will also notice that the TIMESTAMP column was automatically populated, in ascending sequence, in the order in which the rows were inserted, REGARDLESS of which table received the inserted rows.

Now, update one row of each table. Here's some code:

update a set myVarchar = '123' from myTableWithIdentityAndTimestamp a where myId = 1
update a set myVarchar = '123' from myTableWithIdentityAndTimestamp2 a where myId = 2

Now, run the select statements again. Note that in the updated rows, TIMESTAMP has been updated. As long as you don't care WHEN the data has been updated, the timestamp could be used to see what was the last time anything in the entire row has been updated.

In conclusion, the TIMESTAMP column should not be used for date/time comparisons, and probably can't be relied upon for sequencing at all. It only guarantees a unique value within a database. Not only that, but it is a deprecated datatype that should be avoided. Look in MSSQL Books-Online at "rowversion" (the datatype that is replacing timestamp).

Hope this (long-winded) explanation is of some use. Good luck!

thank you......