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?
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; 
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:
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.
Select * from table t1 where date = (Select max(date) from table t2 where t2.id = t1.id) 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 
idanddate) don't work. Hence, you cannot do what you want to do.