none
Get last record in a SQL database

    Question

  • 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.
    Friday, December 09, 2005 11:26 PM

Answers

  • 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
    Monday, December 12, 2005 7:23 PM
  • select top 1 * from TABLE_NAME  order by ID desc

    isn't is what you're asking for ?
    Wednesday, December 14, 2005 10:39 AM
  • 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.

    Saturday, December 17, 2005 1:29 PM

All replies

  • 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. >
    Friday, December 09, 2005 11:45 PM
  • 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')? 
    Saturday, December 10, 2005 1:13 AM
  • > 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 
    
    
    

    Saturday, December 10, 2005 8:07 PM
  • > 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 
    
    
    

    Saturday, December 10, 2005 8:07 PM
  • 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.
    Sunday, December 11, 2005 3:33 PM
  • 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
    Monday, December 12, 2005 7:23 PM
  • 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.
    Monday, December 12, 2005 10:04 PM
  • select top 1 * from TABLE_NAME  order by ID desc

    isn't is what you're asking for ?
    Wednesday, December 14, 2005 10:39 AM
  • 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.

    Saturday, December 17, 2005 1:29 PM
  • 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

     

    Thursday, June 22, 2006 10:07 AM
  • This is the answer... Thanks to you Edward E. Weller
    Wednesday, July 12, 2006 9:32 AM
  • This is the answer. Thanks to you Edward E. Weller

    Wednesday, July 12, 2006 9:32 AM
  • That was really helpfull Thanks
    Wednesday, July 26, 2006 7:27 PM
  • athanks to all fo royu help I got it

     

    Thursday, August 03, 2006 4:57 PM
  • Mr Edward that was very helpfull so I thought I will thank you personnaly
    Thursday, August 03, 2006 5:38 PM
  • 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)

     

     

     

     

     

    Thursday, October 19, 2006 3:28 PM
  • 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
    Wednesday, May 06, 2009 4:00 PM
  • 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 ]
    Friday, May 22, 2009 7:46 AM
  • 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..
    Tuesday, May 26, 2009 7:55 AM
  • Sweet! Right on spot...
    Friday, February 26, 2010 7:21 AM
  • 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
    Wednesday, May 05, 2010 4:56 AM
  • 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

    Saturday, June 19, 2010 10:23 AM

  • 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();
    }
    ...
                      
    Friday, August 27, 2010 1:45 AM
  • CREATE
     FUNCTION
     dbo.AutoIDWithString (@FieldName as
     varchar
    (50),@TableName as
     varchar
    (50))
    RETURNS varchar (800)
    AS
    begin
    declare @RESULT varchar (1000)
    declare @query varchar(1000)

    SET @QUERY = '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'
    EXEC sp_executesql @query
    RETURN @RESULT
    end
    Monday, December 13, 2010 3:22 AM
  • CREATE
     FUNCTION
     dbo.AutoIDWithString (@FieldName as
     varchar
    (50),@TableName as
     varchar
    (50))
    RETURNS varchar (800)
    AS
    begin
    declare @RESULT varchar (1000)
    declare @query varchar(1000)


    set @query = '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

    EXEC sp_executesql @query
    RETURN @RESULT
    end
    Monday, December 13, 2010 3:26 AM
  • Select Top 1 FieldName From TableName Order By FieldName Desc

    OR

    Select Top 1 * From TableName Order By FieldName Desc

    Regards,

    Ibrahim Nizamani

    Thursday, February 02, 2012 2:12 PM
  • Thanks very much for this, all they write nonsense but you did a great job. Excuse the bad English, I am from Croatian
    Friday, July 13, 2012 7:49 PM