How to define a SQL statement to get the last row of an ID (SQL Server)

Question

I need a sql statement for the following case. In a table I have to filter the last data row of an Id. For example:

noID  | Name  | IdentNo | report_created | ... |
1     | testA | 991001  | 2013-01-02     | ... |
1     | testA | 991001  | 2015-06-20     | ... | //this is the last of noID=1
3     | testB | 991002  | 2014-01-23     | ... |
4     | testC | 991003  | 2012-05-02     | ... |
4     | testC | 991003  | 2014-07-30     | ... |
4     | testC | 991003  | 2015-10-11     | ... | //this is the last of noID=4
120   | testC | 991003  | 2016-03-02     | ... |
....

How you can see the unique IdentNo can have several noID. Well, I need a SQL statement to return just the last row of the noID.

This would be the result of the sql statement:

noID  | Name  | IdentNo | report_created | ... |
1     | testA | 991001  | 2015-06-20     | ... |
3     | testB | 991002  | 2014-01-23     | ... |
4     | testC | 991003  | 2015-10-11     | ... |
120   | testC | 991003  | 2016-03-02     | ... |
....

Currently I handle this like below:

SELECT TOP 1 * FROM Test_Table_1 WHERE IdentNo = 991057 ORDER BY report_created DESC

But I have to customize every IdentNo and that's to much.

Answer 1

You could use the sql server partition by clause and then order by report_created desc and then get the first row.

select a.* from 
(select noId,Name,IdentNo,report_created,
row_number over (partition by noId order by report_created desc) as rnum
from your table)a
where a.rnum=1