SQL to retrieve the latest record in a table

[matpj]

Hi,
I have 3 columns from a table (Comments) I need to report on.
1. Comment
2. CreatedDate
3. ProjectNo

I need to return the latest Comment, but when I try to write some SQL and use the GroupBy expression, I have to group by all fields (apart from the Created Date, as i'm using a MAX expression on that field)
can anyone suggest how I can acvhieve this.

currently the SQL is:

SELECT ProjectNo, Comment, MAX(CreatedDate)
FROM Comments
GROUP BY ProjectNo, Comment


as the comment field is a free form text field, this is going to return a row for each comment.
I only want the very latest one.

thanks in advance,

Matt
London (UK)

 

[SkipVought]

Hi,

How is "very latest one" identified as very latest one? Be advised that the ORDER of the table is a reporting technique and it is not a characteristic of the table.

Skip,
[sub]
[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [/sub]

 

[matpj]

the latest one is identified as being the one with the most recent CreatedDate.

I'm using MAX(CreatedDate) to achieve this, but I have to group on both of the other fields.

Matt
London (UK)

 

[PHV]

One way:
SELECT ProjectNo, Comment, CreatedDate
FROM Comments A
WHERE CreatedDate=(SELECT Max(CreatedDate) FROM Comments B WHERE B.ProjectNo=A.ProjectNo);
another way:
SELECT A.ProjectNo, A.Comment, A.CreatedDate
FROM Comments A INNER JOIN (
SELECT ProjectNo, Max(CreatedDate) AS LastDate FROM Comments GROUP BY ProjectNo
) B ON A.ProjectNo = B.ProjectNo AND A.CreatedDate = B.LastDate;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SkipVought]

It there is ONE CreatedDate row, then you have the row with the Comment. It there are more than ONE CreatedDate row, then how will you know which one was actually created last?

Skip,
[sub]
[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [/sub]

 

[matpj]

Skip,
each time a user adds a comment to a project, it created a new record in the table, containing those three rows.

all comments will be different.
I just need the most recent comment (going by the one with the most recent CreateDate Field) for EACH project.

much like PH suggests.
Im just trying PH's query, although the first one seems to be taking a long time to run.


Matt
London (UK)

 

[matpj]

PH,

any idea why the first of the queries doesn't seem to run quickly? its been 10 minutes and nothing....
I've tried the second SQL and I get the error:

"Table reference has to be a table name or an outer join escape clause in a FROM clause."

is there something wrong with the query?

Matt
London (UK)

 

[matpj]

PH,

any ideas?
I'm stuck as the first query just takes about half and hour or so....
and the bottom one doesn't work.

Matt
London (UK)

 

[PHV]

any ideas?
Sure, at least 2.
1) upgrade to a decent version of access
2) create a saved query named, say, qryLastDate:
SELECT ProjectNo, Max(CreatedDate) AS LastDate FROM Comments GROUP BY ProjectNo;
And now your query:
SELECT A.ProjectNo, A.Comment, A.CreatedDate
FROM Comments A INNER JOIN qryLastDate B ON A.ProjectNo = B.ProjectNo AND A.CreatedDate = B.LastDate;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244