How to get last inserted row from table?

Question

My table is like below:

Id  Data   Date 
A   abc    2016-10-01 00:00:00
A   def    2015-05-20 00:00:00
B   xyz    2014-05-20 00:00:00
B   uvw    2016-10-01 00:00:00
B   rst    2015-10-01 00:00:00

I need to get the last inserted row by column Id. So expected output will be:

Id  Data   Date
A   def    2015-05-20 00:00:00
B   rst    2015-10-01 00:00:00

I can be able to get the last inserted row by Identity column or by inserted date column if I have. But how to get last inserted row without these columns?

See this answer http://stackoverflow.com/questions/42648/best-way-to-get-identity-of-inserted-row?rq=1 — Dave Anderson, Sep 22 '16 at 12:04
Possible duplicate of [Get the last inserted row ID (with SQL statement)](http://stackoverflow.com/questions/9477502/get-the-last-inserted-row-id-with-sql-statement) — Turque, Sep 22 '16 at 12:08
Your output doesn't make sense. SQL tables represent *unordered* sets. There is no concept of *last* record, unless a column specifies that ordering. The obvious candidates in your case (`id` and `date`) don't work. Hence, you cannot do what you want to do. — Gordon Linoff, Sep 22 '16 at 12:10
Please check my updated answer and let me know work for you Or not so i can help you ahead — Sandip - Frontend Developer, Sep 22 '16 at 12:52

score 4 · Accepted Answer · answered Sep 22 '16 at 12:04

4

row_number() is the typical way of doing this:

select t.*
from (select t.*,
             row_number() over (partition by id order by date desc) as seqnum
      from t
     ) t
where seqnum = 1;

answered Sep 22 '16 at 12:04

Gordon Linoff

1,242,037
58
646
786

It gives me this output: – good-to-know Sep 22 '16 at 12:05
Id Data Date seqnum A abc 2016-10-01 00:00:00 1 B uvw 2016-10-01 00:00:00 1 – good-to-know Sep 22 '16 at 12:05

Sandip - Frontend Developer · Answer 2 · 2016-09-22T12:18:09.380

Use PARTITION BY AND ORDER BY WITH ROW_NUMBER():

;With T AS 
(
    SELECT
        T1.*,
        T2.NoOfCount,
        ROW_NUMBER() OVER(PARTITION BY T1.Id ORDER BY T1.Id DESC) AS PartNo
    FROM @tblTest T1
    LEFT JOIN (
        SELECT
            ID, COUNT(*) AS NoOfCount       
        FROM @tblTest
        GROUP BY ID
    ) T2 ON T1.ID=T2.ID
)
SELECT
    T.ID,
    T.Data,
    T.Date
FROM T 
WHERE T.PartNo=T.NoOfCount

OR

;With T AS 
(
    SELECT
        T1.*,           
        ROW_NUMBER() OVER(PARTITION BY T1.Id ORDER BY T1.Id DESC) AS PartNo
    FROM @tblTest T1        
),
W AS
(
    SELECT
        ID, COUNT(*) AS NoOfCount       
    FROM @tblTest
    GROUP BY ID
)
SELECT 
    T.ID,
    T.Data,
    T.Date
FROM T
LEFT JOIN W ON T.ID=W.ID
WHERE T.PartNo=W.NoOfCount

Output:

score 0 · Answer 3 · edited May 23 '17 at 11:53

0

Here is an undocumented way:

Equivalent of Oracle's RowID in SQL Server

If you really need to use the row number, use an identity column.

edited May 23 '17 at 11:53

Community

1
1

answered Sep 22 '16 at 12:12

Zuperdudu

1
1

score 0 · Answer 4 · edited Nov 22 '17 at 02:01

0

Select * 
from table t1
where date = (Select max(date)
    from table t2
    where t2.id = t1.id)

edited Nov 22 '17 at 02:01

Nik

1,780
1
14
23

answered Nov 22 '17 at 01:18

Aditya

1

user3295336 · Answer 5 · 2016-09-22T13:29:47.423

-1

This should work:

SELECT TOP 1 * FROM table
ORDER BY Id DESC

In my endeavour to give the simplest solution:

SELECT DISTINCT TOP 2 * FROM table
ORDER BY date DESC

edited Sep 22 '16 at 13:29

answered Sep 22 '16 at 12:58

user3295336

51
10

*"I can be able to get the last inserted row by Identity column or by inserted date column if I have. But how to get last inserted row without these columns?"* - This fails to meet the requirement spelled out in the question. – IInspectable Sep 22 '16 at 13:15
*"I can be able to get the last inserted row by Identity column **or by inserted date column** if I have. But how to get last inserted row **without these columns**?"* – IInspectable Sep 22 '16 at 14:15

How to get last inserted row from table?

5 Answers5