问个SQL问题

Question

表结构

ID. CreatedOn 　　　　　　　　　　　　　　Pid

1    2013-09-26 16:24:52.250                  1
1　　2013-09-26 16:24:52.327                 2
1　　2013-09-26 16:24:52.343                 3
2　　2013-09-27 15:09:07.703                 4
2　　2013-09-27 15:09:07.720                  5
2　　2013-09-27 15:09:07.720                  6
3　　2013-09-28 15:09:07.703                 7
3　　2013-09-28 15:09:07.720                  8
3　　2013-09-28 15:09:07.720                  9
我想知道  如果按照ID来分组  按照CrteateOn作为条件

我想取出最新更改的记录 该怎么SQL语句?

注意可能不止3条ID为1或者2或者3的记录。但是CreateOn日期都是一样的

Answer 1

select * from TableA where id in (select id from TableA where CreatedON=(select Max(createdOn)  as createdon from TableA))

Answer 2

USE&nbsp;tempdbGOIF&nbsp;EXISTS(SELECT&nbsp;*&nbsp;FROM&nbsp;SYSOBJECTS&nbsp;WHERE&nbsp;NAME='TEMP')&nbsp;&nbsp;&nbsp;&nbsp;DROP&nbsp;TABLE&nbsp;TEMPCREATE&nbsp;TABLE&nbsp;TEMP( ID&nbsp;INT, CREATED&nbsp;DATETIME, PID&nbsp;INT)GOINSERT&nbsp;INTO&nbsp;TEMPVALUES(1,'2013-01-01&nbsp;11:11:11',1), (1,'2013-01-01&nbsp;11:11:12',2), (1,'2013-01-01&nbsp;11:11:13',3), (2,'2013-01-01&nbsp;11:11:11',1), (2,'2013-01-01&nbsp;11:11:12',2), (2,'2013-01-01&nbsp;11:11:13',3), (2,'2013-01-01&nbsp;11:11:14',4), (3,'2013-01-01&nbsp;11:11:11',1), (3,'2013-01-01&nbsp;11:11:12',2), (3,'2013-01-01&nbsp;11:11:13',3), (3,'2013-01-01&nbsp;11:11:14',4)GOSELECT&nbsp;*&nbsp;FROM&nbsp;TEMP&nbsp;AS&nbsp;AWHERE&nbsp;NOT&nbsp;EXISTS(SELECT&nbsp;0&nbsp;FROM&nbsp;TEMP&nbsp;AS&nbsp;B&nbsp;WHERE&nbsp;A.ID=B.ID&nbsp;AND&nbsp;A.CREATED<B.CREATED)

Answer 3

select a.*from table as a&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; inner join (select id, max(CreatedOn) as CreatedOn&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; from table&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; group by id) as b on a.id=b.id and a.CreatedOn=b.CreatedOn