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.
"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
ORDER BY yourDate DESC
Just out of curiosity, what happens if more than one project has the same date?
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.