select last or newest entry

[manared]

I was wondering if there was a way in a SELECT statement (or any other way for that matter) to select the very last (or newest) entry in a SQL database from vb.net 2005. Thanks!

 

[Arg81]

There may be an easier approach, but the way I do it is as follows;

SELECT @@IDENTITY FROM MyTable

This will bring back the last ID stored in MyTable (obviously change this to your table name).
Store this value as a parameter. I.E.

dim paramID = me.txtLastIdentity.text

I have txtLastIdentity bound to the result of the first SELECT query.

Then I use a select query based on the ID field (@ID) and fill it using this parameter;

me.tableadapter1.fillbyID(me.dataset1.table, paramID)

^^ here you obviously change tableadapter1 to your correct table adapter, fillbyID to the name of your query, and me.dataset1.table to me.(whatever your dataset is called).(whatever your datatable is called)
It will then fill the datatable with whatever value the paramID is, hopefully all being well your last inserted ID...

 

[Arg81]

Interesting I just found a similar solution (I think).

If your SQL table stores the data in the fields so that the newest entry is the last entry (mine is this way) then you can simply use;

SELECT TOP 1 * FROM MyTable ORDER BY IDFIELD desc

The reason I've put ORDER BY IDField in there is because you can select the top 5 (or whatever number you need) if needed and sort them so the newest is first ....

Hope one or the other helps!

 

[cjard]

I was wondering if there was a way in a SELECT statement (or any other way for that matter) to select the very last (or newest) entry in a SQL database from vb.net 2005. Thanks!

If your database doesnt include a timestamp of some sort as a column, you cannot do this.. Its nothing to do with VB either..

 

[cjard]

SELECT TOP 1 * FROM MyTable ORDER BY IDFIELD desc

Personally, I think its risky to assume that natural ordering of a DB table is order-of-insertion.. While you cant be sure that this is the case, you can be sure that a timestamp column that defaults to NOW, when used with a MAX() query, will give you the most recent row..

 

[Arg81]

yeah that's a safe assumption but as I say, if you know that your SQL table is in order-of-insertion by using the ID field to do so, then I don't see anything wrong with using this.

I saw something about the timestamp and MAX() a while ago, but didn't want to have to alter all my tables.

 

[cjard]

if you know that your SQL table['s natural ordering] is in order-of-insertion

True, but can you guarantee your assumption by quoting a statement from the DB manufacturer that natural order is order-of-insertion?

For oracle, natural order is order-on-disk, which is very different to order-of-insertion when deleted space is re-used.. Dodgy ground!

 

[Arg81]

ah, I haven't played with Oracle at all, only really MS SQL...

Learn something new everyday they say, I'll keep that order-on-disk note to one side, just in case