Results 1 to 2 of 2

Thread: Querying unique record without using Distinct & GroupBy

  1. #1
    Join Date
    Feb 2011
    Posts
    1

    Querying unique record without using Distinct & GroupBy

    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

  2. #2
    Join Date
    May 2006
    Posts
    407
    In order to have a query you can update, I have made the (Select Distinct) query an append query and write the records into a work table. Then I can use that table (the work table) and the table I NEED to query, make a join between the two on AuditID and you should be able to check the check box that you need to be checked.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •