By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,478 Members | 1,601 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,478 IT Pros & Developers. It's quick & easy.

Return Record Matching Latest Date

P: 9
Hi there,
I have a table, which has an order number, a comment & a date the comment was added.

Therefore you could have 3 or 4 comments against one order number. All I want to do is create a table via a query which takes my existing table and shows me the latest comment based on the date. So for example, if there are two comments for an order, one done today and one yesterday I want my query to show me only the comment done today.

I thought this would be easy, but when I use the MAX on the date on the query designer I am still getting two rows of data for some orders.

I just can't seem to get my head around what I am doing wrong.
Dec 28 '11 #1

✓ answered by NeoPa

First of all let me warn you against duplicating the pre-existing data into a separate table. This is very rarely a good idea (See Database Normalisation and Table Structures). Instead, just use the SELECT query when that specific data is required.

Next, please check the new title of the thread. This is not only a valid title, but it also gives you clues as to what you're actually trying to do. Please remember in future to use a sensible title for any new question threads.

Now let's look at your actual issue. You're not looking to return the value of the maximum date, but actually you want the record associated with that maximum. This can be done in the following format :
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM   [YourTable] AS [YTo]
  3. WHERE  [CommentDate] In(SELECT Max([CommentDate])
  4.                         FROM   [YourTable] AS [YTi]
  5.                         WHERE  YTi.OrderNo = YTo.OrderNo)
Does that make sense?

Share this Question
Share on Google+
4 Replies


Expert Mod 2.5K+
P: 2,545
Please post the SQL for the query concerned - we can't answer you without having the details of the tables and fields concerned in front of us.

-Stewart
Dec 28 '11 #2

NeoPa
Expert Mod 15k+
P: 31,709
First of all let me warn you against duplicating the pre-existing data into a separate table. This is very rarely a good idea (See Database Normalisation and Table Structures). Instead, just use the SELECT query when that specific data is required.

Next, please check the new title of the thread. This is not only a valid title, but it also gives you clues as to what you're actually trying to do. Please remember in future to use a sensible title for any new question threads.

Now let's look at your actual issue. You're not looking to return the value of the maximum date, but actually you want the record associated with that maximum. This can be done in the following format :
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM   [YourTable] AS [YTo]
  3. WHERE  [CommentDate] In(SELECT Max([CommentDate])
  4.                         FROM   [YourTable] AS [YTi]
  5.                         WHERE  YTi.OrderNo = YTo.OrderNo)
Does that make sense?
Dec 28 '11 #3

P: 9
Thank you very much for your help, I've tried what you said above and it worked straight away.

Thanks again.
Dec 28 '11 #4

NeoPa
Expert Mod 15k+
P: 31,709
I'm very pleased to help, and almost more pleased even to hear that what I knocked up worked first time. Very gratifying :-)
Dec 28 '11 #5

Post your reply

Sign in to post your reply or Sign up for a free account.