Sql Query Help!

[Sabin_33]

I am using VB6 with SQL SErver 2000.

I want to create a query the the last x records inputed.
Anybody got any ideas?

[mmetzger]

If you're using an integer primary key, try something like:

select top 10 * from mytable order by id DESC

Change for your requirements...

[exterminator]

If you want the last 10 records (meaning the latest inserted data into the table) then to achieve this you need to have a Timestamp field in your table. And then use the field Timestamp instead of 'id' as in mmetzger's post (order by clause). Regards.

[mmetzger]

Would an auto-increment id field perform the same thing? The values would never be reused so the highest values would be the last entries made.

[exterminator]

Would an auto-increment id field perform the same thing? The values would never be reused so the highest values would be the last entries made.

Yes, it would! But they work upon the restriction that a table must have an auto-increment id field which might not always be the case. Anyways, I have been wondering about this - what happens when we reach the upper limit for the value of an auto-increment id field due to frequent inserts and deletes? What value would the next record get? Regards.

[Sabin_33]

Well Thank you for your replies.
Is there any way I could do this without having a TimeStamp or AutoIncrement field?

[exterminator]

Alternative? why so? By the way, what you can do is make a small table and keep the information about the latest x records inserted over there - you might use the INSERT triggers for this purpose. Or, you could keep this information into some kind of a log file and then while retrieving use it. Regards.

[Sabin_33]

Well I added a timestamp field but every adding a record I got an error. What I'll do is just create a regular smalldatetime field and insert GETDATE() for every record insterted.

BTW: I read this about timestamp field
http://msdn.microsoft.com/library/de...ta-tz_6fn4.asp
Which is saying it really good as a unique value. Becuase it changes even if you modify a record.

Thank you much

[exterminator]

Listen I did not say that the Identity column solution by mmetzger was not a solution but I said that it would depend if you had such a column in the table of interest.

Now coming to datetime/timestamp. What you looked at is good you came to know about a new datatype. What I meant is make a column named DateStamp or TimeStamp and enter the values into that column yourself filling in the value i.e. making it a part of your insert statement. Now, if you want it not to change when you update the records then simply don't pass a value for this columns update. And now when you will select records based on timestamp/datestamp you will get the newest "inserted" records. Do not let the database fill in the value for you. So your insert statement would look something like:

INSERT INTO <tablename> values (<field1>, <field2>,...,<timestamp for example: '10/11/2005 12:00:00'>) //as per the database's format of acceptance...

Where from will you get this datetime value? Using a function that would give you the time and date for "now". Remember not to update this value in the update statement if your main aim is to achieve selecting newest "inserted" records. Hope this is clear. Regards.

[trends]

Dear All,

I want to now one thing.what he ased is to get the last ten inserted records.I want to know whether that includes the last updated records also.Because the last modified record also can be included as the last inserted records.

so in this case the timestamp will be of really help.If there is any suggesstion about this please tell.



Thank you.

[mmetzger]

You'd need to have a datetime field that had an update time added when you update the row. You'd then modify the query to sort by that field.

[erickwidya]

I have been wondering about this - what happens when we reach the upper limit for the value of an auto-increment id field due to frequent inserts and deletes?

i've been wondering about this one too but i guess no one lives long enough to tell the truth

Where from will you get this datetime value?

i guess what Sabin_33 means is using TimeStamp Data Type not Columns that contain date and time value like exterminator, mmetzger means