I am trying to retrieve unique records using select query. If I use Distinct or GroupBy funtion I am able to achieve the desired output, however I am not able to tick the check box (Reminded) when I run the query.

I have table with data as follows,

AuditID Date Reminded
-----------------------------------------
1203 01/01/2010 No
1203 05/01/2010 No
1204 06/01/2010 No
1204 06/01/2010 No
1204 07/01/2010 No
1205 07/01/2010 No
1206 06/01/2010 No
1206 06/01/2010 No

I want to achieve the following outout
---------------------------------------------

Reminded AuditID Date
---------------------------------------------
No 1203 05/01/2010
No 1204 07/01/2010
No 1205 07/01/2010
No 1206 06/01/2010


I am looking to retieve unique AuditID with the latest date.

I tried the below queries

select b.reminded,b.auditid from RiskActionPlan b where date=(select max(date) from RiskActionPlan where auditid=b.auditid)

I am able to select the Reminded Checkbox when I run the above query however the it does NOT result with unique auditid (audit ids are repeted)

If i add distinct (Select distinct ....) I am able to get the unique records but i am not able to check the Reminded checkbox.


I have been trying to sort this out since last 4 hours, I will appreciate If someone could assist me with this.

Thanks in advance.
Andy