Query latest record

[acdevteam@gmail.com]

Hi All,

I need some help with a query.
Here is the structure for the table:
ID, sDate, sComment, sRole

Possible values in the table:
0101, 11/6/2006, Comment123, Role1
0101, 11/6/2006, Comment124, Role1
0101, 11/6/2006, Comment125, Role2
0101, 11/6/2006, Comment126, Role2

As you see, ID is not a primary key.
We would like the query to return the latest Comment for each ID and
sRole.

eg. if we pass ID=0101 and sRole = Role1, we would like the query to
return Comment124, based upon the timestamp (though the date is same,
the time is later for that record.

Any suggestions?

Thanks,
Paul

[Smartin]

Re: Query latest record

acdevteam@gmail .com wrote:

Hi All,
>
I need some help with a query.
Here is the structure for the table:
ID, sDate, sComment, sRole
>
Possible values in the table:
0101, 11/6/2006, Comment123, Role1
0101, 11/6/2006, Comment124, Role1
0101, 11/6/2006, Comment125, Role2
0101, 11/6/2006, Comment126, Role2
>
As you see, ID is not a primary key.
We would like the query to return the latest Comment for each ID and
sRole.
>
eg. if we pass ID=0101 and sRole = Role1, we would like the query to
return Comment124, based upon the timestamp (though the date is same,
the time is later for that record.
>
Any suggestions?
>
Thanks,
Paul
>

Untested SQL:

SELECT TOP 1 sComment
FROM tblPaul
WHERE ID = '0101'
AND sRole = 'Role1'
ORDER BY sDate DESC;

--
Smartin