Home » SQL ServerRSS

Get last record in a SQL database

Don't know if this is the right SQL section, but... using VBE, how do you SQL for the last record in the database?

Thanks.
 

23 Answers Found

 

Answer 1

What does "last" mean? A table is an unordered set of rows, so you need to indicate to SQL Server what you mean by "last"... wrote in message news:c6e41dc5-463e-4bfd-b88c-99ebbc38e746@discussions.microsoft.com... > Don't know if this is the right SQL section, but... using VBE, how do > you SQL for the last record  in the database? > > Thanks. >
 

Answer 2

the LAST record  entered in the database.... if for example if you entered 10 records in a new database, how do you fetch #10 - or the LAST recorded
entered (without indicating 'get the 10th record')? 
 

Answer 3

> the LAST record  entered in the database.... if for example if you
> entered 10 records in a new database, how do you fetch #10 - or the LAST
> recorded
> entered (without indicating 'get the 10th record')?

Again, SQL Server doesn't keep track of which row is the newest.  This is 
not Access, and you are not stuffing a line of data at the end of a text 
file.

What does your table structure look like?  Do you have a naturally 
increasing column (such as IDENTITY, or DATETIME reflecting when the row was 
created)?

A 


 

Answer 4

> the LAST record  entered in the database.... if for example if you
> entered 10 records in a new database, how do you fetch #10 - or the LAST
> recorded
> entered (without indicating 'get the 10th record')?

Again, SQL Server doesn't keep track of which row is the newest.  This is 
not Access, and you are not stuffing a line of data at the end of a text 
file.

What does your table structure look like?  Do you have a naturally 
increasing column (such as IDENTITY, or DATETIME reflecting when the row was 
created)?

A 


 

Answer 5

I have a ID that numbers each record  (key).... I was thinking along the line of
SELECT MAX id but wasn't sure if there was a better way of doing so.
 

Answer 6

If you want to select the last ROW inserted in a TABLE in a DATABASE that has an IDENTITY column named ID, you could use the following:

SELECT *
FROM    TABLE
WHERE  ID = IDENT_CURRENT('TABLE')

or

SELECT *
FROM    TABLE
WHERE   ID = (SELECT MAX(ID)  FROM TABLE)

I hope this helps.

Sincerely,
Edward E. Weller
 

Answer 7

Thanks.  I haven't yet been able to try all the great ideas I got... still wasn't
able to open the database... so I'm reading a book.
 

Answer 8

select top 1 * from TABLE_NAME  order by ID desc

isn't is what you're asking for ?
 

Answer 9

I'd recommend using the IDENT_CURRENT function. However it is limited to one table. So if you're looking for the last entered id across all tables...well it's a bit more complex. Unless you use some kind of scheme that identifies what table an ID belongs too. I've seen this used at companies where they have a single generator for all IDs. But I digress. Select * from table_name where id=IDENT_CURRENT('table_name') Should do what you want.
 

Answer 10

Dear Valeriy,

Your reply has helped me a lot !!!! I have been searching for this answer since a long time and didn't get any answer from any where !!!!I am extremely thankful to you.

May God Bless you always

Thanks

Maleeha

 

Answer 11

This is the answer... Thanks to you Edward E. Weller
 

Answer 12

This is the answer. Thanks to you Edward E. Weller

 

Answer 13

That was really helpfull Thanks
 

Answer 14

athanks to all fo royu help I got it

 

Answer 15

Mr Edward that was very helpfull so I thought I will thank you personnaly
 

Answer 16

This appears to work only if the current ID is the last ID. After restarting the program, it selected the current ID - the first record.

SELECT *
FROM    TABLE
W
HERE  ID = IDENT_CURRENT('TABLE')

This one picks up the max ID as in last record  added.

SELECT *
FROM    TABLE
WHERE   ID = (SELECT MAX(ID)  FROM TABLE)

 

Answer 17

Take This Table


NIC1234  ANNE
NIC1324  JOHON
NIC3333  FRANK


Here we have to avoid first 3 letters before generate latest number.We do by using right Function.
Here stored procedure. here 3 is no of Char letters.

CREATE PROCEDURE dbo.get_last_rec_no_sp
 @key varchar(20),
 @tbl varchar(50)   
AS   
--SELECT MAX(CAST(RIGHT(empNo,LEN(empNo)-3)+1 AS INT))FROM dbo.tblEmployee
DECLARE @sql nvarchar(4000)
SELECT @sql = 'SELECT MAX(CAST(RIGHT('+@key+',LEN('+@key+')-3)+1 AS INT))FROM dbo.'+quotename(@tbl)
EXEC sp_executesql @sql
 

Answer 18

how can I make this as a function?I'm using sq Server 2005

SELECT MAX(CAST(LEFT(Customer_ID,2)AS char(5))) +
            RIGHT('000' + CAST(MAX(CAST(RIGHT([field_name ],LEN([field_name ])- 2)+ 1 AS int))AS char(5)),3)
FROM [table_name ]
 

Answer 19

hi i have this code in sql  server 2005 but gives me an error

this is the error:

Msg 1087, Level 15, State 2, Procedure AutoIDWithString, Line 9
Must declare the table variable "@TableName".

and here is the function:

CREATE FUNCTION dbo.AutoIDWithString (@FieldName as varchar(50),@TableName as varchar(50))
RETURNS varchar(800)
AS
begin
declare @RESULT varchar(1000)

SELECT @RESULT = MAX(CAST(LEFT(@FieldName,2)AS CHAR(2))) +
RIGHT('000' + CAST(MAX(CAST(RIGHT(@FieldName,LEN(@FieldName)-2)+1 AS INT))AS VARCHAR(5)),3)
FROM @TableName
RETURN @RESULT
end
i don't know how to pass the table name as parameter..
please help..thanks in advance..
 

Answer 20

Sweet! Right on spot...
 

Answer 21

select top 1 * from TABLE_NAME  order by ID desc

isn't is what you're asking for ?
Thank you very much. I used this as a hint to retrieve a specific column from the last record. :-) Thanks again
 

Answer 22

if my table structure is like

suppose table name emp

emp(name,address) and if i entry five record  in table then how i can find last record from table

 

Answer 23


linq Example;
last record  ID capture in runtime

order o = new order()
{

custormerID = Convert.ToInt32(listView1.SelectedItems[0].Tag),
date = DateTime.Now,
orderNr = null,
.
.
.
.

};
try
{
  db.order.InsertOnSubmit(o);
         db.SubmitChanges();
         o.orderNr = "KZM-" + o.orderID.ToString().PadLeft(5, '0');
         db.SubmitChanges();
}
...
                  
 
 
 

<< Previous      Next >>


Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter