You must Sign In to post a response.
  • Category: SQL Server

    How to get last inserted record without top/max clause


    Are you looking for a way to get last inserted record in sql ? want to do it without top/max clause ? then read this thread to know more about it



    Hi All,

    Please send me SQL Queries which are asking in interview for experienced person.

    On Saturday, I faced one interview in .net but they give me one paper that contain only SQL query question's and write query.

    One question that i remember is :

    How do we get the value of the identity column of the last inserted records without using a Max / Top clause.

    And other queries related to group by and having clause.

    So send me queries on this.
  • #743961
    hii,

    we get the value of the identity column of the last inserted records without using a Max / Top clause.

    by using @@identity.
    Its gives you the last value inserted in the identity column.

    Hope this helps you

    Regards
    Shalini

  • #743964
    Hi,

    Regarding the question the was asked on identity if you are having the column as identity then you can use the system function @@IDENTITY which has the most recent IDENTITY value for the current connection,

    regarding the questions asked frequently you can see the link

    http://blog.sqlauthority.com/2011/07/01/sql-server-interview-questions-and-answers-frequently-asked-questions-introduction-day-1-of-31/

  • #743968

    Hai Darshini,
    There are some global variables defines in sql server architecture which returns the values as the global e.g.
    @@CONNECTIONS,@@MAX_CONNECTIONS,@@CPU_BUSY,@@ERROR and @@IDENTITY etc.
    here @@IDENTITY is to get the recently inserted identity value.
    The other questions can be something like:
    1. Get the nth highest salary without using TOP clause.
    2. Why Having clause if we have the Where clause to filter the records.
    3. Can we use the where clause with having clause
    4. Is the Order By clause must for using the Having clause?
    5. How the indexes works internally.
    6. Why more than one Clustered index not allowed for a table.
    7. Do you think that having more indexes degrades the performance of the query? Id so then why?
    Hope it will be helpful to you.


    Regards,
    Pawan Awasthi(DNS MVM)
    +91 8123489140 (whatsApp), +60 14365 1476(Malaysia)
    pawansoftit@gmail.com

  • #743972
    Hi Pawan,

    Can you please send me more question like this... for 4+ year exp.

    and send me query for below question

    How do we get the value of the identity column of the last inserted records without using a Max / Top clause.

    Regards,
    Darshana.

  • #743976
    see below cursor, it may help you to get last inserted record

    DECLARE @testVar NVARCHAR(100)
    DECLARE testCursor CURSOR
    DYNAMIC
    FOR
    SELECT MESSAGE FROM ELMAH_ERROR
    OPEN MYTESTCURSOR
    FETCH LAST FROM testCursor INTO @testVar
    CLOSE testCursor
    DEALLOCATE testCursor
    SELECT @testVar

    OR
    you can use Last function to get latest inserted record
    SELECT LAST(col1) AS LastOrderPrice FROM table1

    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #743977
    Thank you Prasad.

    Could you please tell me more such types of questions. This will help me lot to clear the interview.

  • #744028
    Hi,

    In your table if you have created date column then use order by clause desc order it will give last inserted information as first.

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #744074
    HI..



    create table fin(Id int identity(1,1),name char(10))

    insert into fin values('Kannan'),('Kumar'),('Senthil')

    select * from fin


    set rowcount 1
    SELECT name FROM fin ORDER BY Id DESC

    --SELECT LAST(Id) AS LastOrderPrice FROM fin


  • Sign In to post your comments