Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Posts
    182

    Unanswered: Query to return only the latest version ??

    Hi
    I need to run a query that will show only the latest revision of my plan. I have 2 tables one with the plans and one with the revisions linked by the plan id. How do I do this ?

  2. #2
    Join Date
    Sep 2003
    Location
    MN US
    Posts
    313
    This thread may help you.

    http://www.dbforums.com/t993406.html

    If you need more than this, you could post the SQL statement here and I'd be happy to take a look.

    HTH,

    Chris

  3. #3
    Join Date
    Apr 2004
    Posts
    182
    SELECT [CTD_Number],[CID],[Part_No],tblDrawings.drawingid, [Part_Name],[Rev_no]FROM tblDrawings,tbldrawRev WHERE tblDrawings.drawingid = tblDrawRev.drawingid AND tblDrawings.cid = 2AND tblDrawings.Deleted = 0 ORDER BY CTD_Number DESC

    I only want the latest revision but I may have to use the date as Reviosn numbers can vary in format.

  4. #4
    Join Date
    Sep 2003
    Location
    MN US
    Posts
    313
    I have to be in a meeting for a couple hours, so I'll post after that.
    Do you currently have a field for revision date or is it reflected in the rev_no...ctd_number?

  5. #5
    Join Date
    Apr 2004
    Posts
    182
    Yes there is a date field.

  6. #6
    Join Date
    Sep 2003
    Location
    MN US
    Posts
    313
    It would be something like this. If you use a date field, you would substitute that for Rev_No in the WHERE clause. Hope this helps.

    SELECT [CTD_Number],[CID],[Part_No],tblDrawings.drawingid, [Part_Name],[Rev_no]
    FROM tblDrawings,tbldrawRev
    WHERE ((tbldrawRev.Rev_No) IN (SELECT TOP 1 [Rev_No] FROM tbldrawRevtblDrawings.drawingid = tblDrawRev.drawingid AND (tblDrawings.cid = 2) AND (tblDrawings.Deleted = 0) ORDER BY CTD_Number DESC

Posting Permissions

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