Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2006
    Posts
    4

    Unanswered: Need help on an SQL query to return top value

    I have a table that I need to pull only the record with the most recent date for each project listed in the table.

    For instance, my fields are PROJECT_ID_NBR, Note_Date, User_ID, and Case_Note.

    What I need is for each Project_ID_Nbr, I need to pull the record with the most recent date.

    I'm pretty new to SQL, but I imagine this isn't that complex. I found a post with something similar using the 'Top 1' field, but I couldn't adapt it to work. I also found a microsoft knowledge base article somewhat related, but again, couldn't make it work.

    Any help would be greatly appreciated! Thanks

    Nate

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    "Top 1" would work. It returns the first record of a given set of data. So, if you were to create a query that returned all of your records in DESCENDING order by date, the first record would be the one with the highest date right?

    SELECT TOP 1 yourFields
    FROM yourTable
    ORDER BY yourDate DESC


    Just out of curiosity, what happens if more than one project has the same date?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Feb 2006
    Posts
    4
    I tried what you suggested at first. What it does is return just the top 1 record with the highest note_date value in the whole table.

    To answer your question, since I have four case notes with the same date, it actually returns all four.

    Logically, it seems like I need to call each project individually, grab the top 1 case note, then aggregate them together. I though about doing it programmically, but I figured with SQL, there might be an easier way.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    One could use a correlated sub query or a derived table for that so one got all ones info in one statement
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Oh, you need a list of latest case notes for EVERY project. Well, that's a bit different.

    Max() could get you where you want to be.

    WHERE yourDate = Max("[yourDate]", "[yourTable]", "some criteria")
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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