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
  • Hi

    Just want to add, to search 5 rows from last record you can use this,

    SELECT *
    FROM   ArcSnapshot
    WHERE  ID <= IDENT_CURRENT('ArcSnapshot')
    AND ID >= IDENT_CURRENT('ArcSnapshot') - 5

    Hope this may help :)

    Regards,

    Nadia

    Thursday, February 26, 2015 8:34 AM
  • Thank you. This worked.
    Wednesday, March 11, 2015 9:47 AM
  • I began looking at solutions that used my Identity field but realized that Valeriy's answer was needed because my identity field was only used to order the rows that had the same date entry.  Seeing that answer made me realize That I needed the "ORDER BY" clause (with "DESC").  Thanks!
    Tuesday, February 23, 2016 5:17 AM