Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2009
    Posts
    5

    Unanswered: Select records where only one column is distinct

    head is mangled. need help trying to create query on all records to select values which needs one column being distinct (document) but has the latest date

    Record Data

    ID | Document | Title | Date
    --------------------------------------------
    1 | MRV-01 | Document A | 04-02-13
    2 | MRV-01 | Document B | 05-02-13
    3 | MRV-01 | Document C | 06-02-13


    Trying to get the following result

    ID | Document | Title | Date
    --------------------------------------------
    3 | MRV-01 | Document C | 06-02-13



    Any help appreciated
    thanks,
    S

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Group by the documents and use the max or last on the date feild
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Oct 2009
    Posts
    5
    Thanks Myle

    This works great if i omit the ID Field. I need to include ID field in report. If i include the ID field in the query it needs to be grouped also and so it returns all records ?

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This should work (with Tbl_Docs being the name of the table):
    Code:
    SELECT Tbl_Docs.ID, Tbl_Docs.Document, Tbl_Docs.Title, Tbl_Docs.Date
      FROM Tbl_Docs INNER JOIN
         ( SELECT Tbl_Docs.Document, Max(Tbl_Docs.Date) AS MaxDate
           FROM Tbl_Docs
           GROUP BY Tbl_Docs.Document) d
        ON Tbl_Docs.Document = d.Document AND
           Tbl_Docs.Date = d.MaxDate;
    Have a nice day!

  5. #5
    Join Date
    Oct 2009
    Posts
    5
    Thanks Sinndho. I am trying to apply it but i have another problem where the data is actually spread over two tables as below (i tried to simplify the record data in my first post above)


    Record Data

    Table 1
    ID | Document | Title
    --------------------------------------------
    1 | MRV-01 | Document A
    2 | MRV-01 | Document B
    3 | MRV-01 | Document C

    Table 2
    ID | Date
    --------------------------------------------
    1 | 04-02-13
    2 | 05-02-13
    3 | 06-02-13

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Then use 2 subqueries:
    Code:
    SELECT a.ID, a.Document, a.Title, a.Date
    FROM ( SELECT Table1.ID, Table1.Document, Table1.Title, Table2.Date
             FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID) AS a
    INNER JOIN ( SELECT Table1.Document, MAX(Table2.Date) AS MaxDate
                   FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID
               GROUP BY Table1.Document) AS b
    ON a.Document = b.Document AND a.Date = b.MaxDate
    Have a nice day!

Posting Permissions

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