# Latest Records depending on Last Modified Date

### Question

• Hi there,

I have to get the latest records from the table depending on Last Modified date.

Table:

ID  Function   Apply        Approved    Withdrawn   LastModifiedDate
1    Coding    1/1/2010   15/1/2010   21/1/2010     21/1/2010 3:58:23
1    Coding    1/1/2010    NULL           NULL           21/1/2010 4:58:23
2    Testing    1/1/2010   15/1/2010   21/1/2010    21/1/2010 3:58:23
3    Analysis  1/1/2010    NULL           NULL           21/1/2010 4:58:23

Output is needed on the basis of last modified date if same fucntion
comes more than 1 time. else all other functions need which appears once in table.

ID  Function  Apply      Approved    Withdrawn        LastModifiedDate
1    Coding   1/1/2010     NULL           NULL            21/1/2010 4:58:23
2    Testing   1/1/2010   15/1/2010   21/1/2010      21/1/2010 3:58:23
3    Analysis 1/1/2010     NULL            NULL           21/1/2010 4:58:23

Thanks,

Thursday, August 26, 2010 5:10 AM

• Take a look for the solutions and explanations of this problem:

Including an Aggregated Column's Related Values
Including an Aggregated Column's Related Values - Part 2
Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog
Friday, August 27, 2010 9:36 PM
• ```select *
from MyTable t where not exists (select * from MyTable where id=t.id and LastModifiedDate>t.LastModifiedDate )

```

http://www.t-sql.ru
• Marked as answer by Wednesday, September 01, 2010 9:19 AM
Thursday, August 26, 2010 5:43 AM
• Hi,

Which version of SQL Server are you using? If you are using SQL Server 2005 or above, please refer to the following sample:

DECLARE @T TABLE

(

ID INT,

Fuction NVARCHAR(20),

[Apply]  NVARCHAR(20),

Approved NVARCHAR(20),

Withdrawn NVARCHAR(20),

LastModifiedDate DATETIME

)

INSERT INTO @T VALUES(1,'Coding','2010-01-01','2010-01-15','2010-01-21','2010-01-21 3:58:23')

INSERT INTO @T VALUES(1,'Coding','2010-01-01',NULL,NULL,'2010-01-21 4:58:23')

INSERT INTO @T VALUES(2,'Testing','2010-01-01',' 2010-01-15','2010-01-21','2010-01-21 3:58:23')

INSERT INTO @T VALUES(3,'Analysis','2010-01-01',NULL,NULL,'2010-01-21 1:58:23')

INSERT INTO @T VALUES(3,'Analysis','2010-01-01',NULL,NULL,'2010-01-21 4:58:23')

;WITH CTE

AS

(

SELECT *, ROW_NUMBER() OVER (PARTITION  BY ID ORDER BY LastModifiedDate DESC )AS OrderNO FROM @T

)

SELECT * FROM CTE WHERE OrderNO=1

Thanks,

Ai-Hua Qiu

Constant dropping wears away a stone.
• Proposed as answer by Monday, August 30, 2010 4:11 AM
• Marked as answer by Wednesday, September 01, 2010 9:20 AM
Monday, August 30, 2010 1:55 AM

### All replies

• ```select top 1 with ties *
from MyTable
order by row_number() over (partition by id order by LastModifiedDate desc)
```

http://www.t-sql.ru
Thursday, August 26, 2010 5:19 AM
• Thanks Alexey,

Solution is giving me only one row, could u plz explain order by clause.

Thursday, August 26, 2010 5:32 AM
• ```select *
from MyTable t where not exists (select * from MyTable where id=t.id and LastModifiedDate>t.LastModifiedDate )

```

http://www.t-sql.ru
• Marked as answer by Wednesday, September 01, 2010 9:19 AM
Thursday, August 26, 2010 5:43 AM
• Take a look for the solutions and explanations of this problem:

Including an Aggregated Column's Related Values
Including an Aggregated Column's Related Values - Part 2
Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog
Friday, August 27, 2010 9:36 PM
• Hi,

Select ID, Function, Apply, Approved, Withdrawn, LastModifiedDate From Table1 Where LastModifiedDate = (Select Max(SubQry.LastModifiedDate) From Table1 As SubQry Where Table1.ID = SubQry.ID And Table1.Function = SubQry.Function And Table1.Apply = SubQry.Apply And Table1.Approved = SubQry.Approved And Table1.Withdrawn = SubQry.Withdrawn)

Best Wishes, Arbi Please vote if you find the posting was helpful.
Friday, August 27, 2010 10:15 PM
• Hi,

Which version of SQL Server are you using? If you are using SQL Server 2005 or above, please refer to the following sample:

DECLARE @T TABLE

(

ID INT,

Fuction NVARCHAR(20),

[Apply]  NVARCHAR(20),

Approved NVARCHAR(20),

Withdrawn NVARCHAR(20),

LastModifiedDate DATETIME

)

INSERT INTO @T VALUES(1,'Coding','2010-01-01','2010-01-15','2010-01-21','2010-01-21 3:58:23')

INSERT INTO @T VALUES(1,'Coding','2010-01-01',NULL,NULL,'2010-01-21 4:58:23')

INSERT INTO @T VALUES(2,'Testing','2010-01-01',' 2010-01-15','2010-01-21','2010-01-21 3:58:23')

INSERT INTO @T VALUES(3,'Analysis','2010-01-01',NULL,NULL,'2010-01-21 1:58:23')

INSERT INTO @T VALUES(3,'Analysis','2010-01-01',NULL,NULL,'2010-01-21 4:58:23')

;WITH CTE

AS

(

SELECT *, ROW_NUMBER() OVER (PARTITION  BY ID ORDER BY LastModifiedDate DESC )AS OrderNO FROM @T

)

SELECT * FROM CTE WHERE OrderNO=1

Thanks,

Ai-Hua Qiu

Constant dropping wears away a stone.
• Proposed as answer by Monday, August 30, 2010 4:11 AM
• Marked as answer by Wednesday, September 01, 2010 9:20 AM
Monday, August 30, 2010 1:55 AM