<p>It's common to have a table where for example the the fields are account, value, and time.  What's the best design pattern for retrieving the last value for each account?  Unfortunately the last keyword in a grouping gives you the last physical record in the database, not the last record by any sorting.  Which means IMHO it should never be used.  The two clumsy approaches I use are either a subquery approach or a secondary query to determine the last record, and then joining to the table to find the value.  Isn't there a more elegant approach?</p>

[{'answer_id': 47431, 'author': 'Tom', 'author_id': 3715, 'author_profile': 'https://Stackoverflow.com/users/3715', 'pm_score': 2, 'selected': True, 'text': '<p>The subquery option sounds best to me, something like the following psuedo-sql.  It may be possible/necessary to optimize it via a join, that will depend on the capabilities of the SQL engine.</p>\n\n<pre><code>select * \nfrom table \nwhere account+time in (select account+max(time) \n                       from table \n                       group by account \n                       order by time) \n</code></pre>\n'}, {'answer_id': 47443, 'author': 'shsteimer', 'author_id': 292, 'author_profile': 'https://Stackoverflow.com/users/292', 'pm_score': 2, 'selected': False, 'text': "<p>could you not do:</p>\n\n<pre><code>select account,last(value),max(time)\nfrom table\ngroup by account\n</code></pre>\n\n<p>I tested this (granted for a very small, almost trivial record set) and it produced proper results.</p>\n\n<p>Edit:</p>\n\n<p>that also doesn't work after some more testing.  I did a fair bit of access programming in a past life and feel like there is a way to do what your asking in 1 query, but im drawing a blank at the moment.  sorry.</p>\n"}, {'answer_id': 47456, 'author': 'Dhaust', 'author_id': 242, 'author_profile': 'https://Stackoverflow.com/users/242', 'pm_score': 1, 'selected': False, 'text': '<p>This is a good trick for returning the last record in a table:  </p>\n\n<pre><code>SELECT TOP 1 * FROM TableName ORDER BY Time DESC  \n</code></pre>\n\n<p>Check out <a href="http://www.quackit.com/sql/tutorial/sql_top.cfm" rel="nofollow noreferrer">this site</a> for more info.</p>\n'}, {'answer_id': 47502, 'author': 'Knox', 'author_id': 4873, 'author_profile': 'https://Stackoverflow.com/users/4873', 'pm_score': 0, 'selected': False, 'text': "<p>@shs<br>\nyes, that select last(value) SHOULD work, but it doesn't...  My understanding although I can't produce an authorative source is that the last(value) gives the last physical record in the access file, which means it could be the first one timewise but the last one physically.  So I don't think you should use last(value) for anything other than a really bad random row.</p>\n"}, {'answer_id': 47511, 'author': 'Knox', 'author_id': 4873, 'author_profile': 'https://Stackoverflow.com/users/4873', 'pm_score': 1, 'selected': False, 'text': '<p>@Tom\nIt might be easier for me in general to do the "In" query that you\'ve suggested.  Generally I do something like</p>\n\n<pre><code>select T1.account, T1.value\nfrom table T as T1\nwhere T1 = (select max(T2.time) from table T as T2 where T1.account = T2.Account) \n</code></pre>\n'}, {'answer_id': 86354, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 0, 'selected': False, 'text': '<p>Perhaps the following SQL is clumsy, but it seems to work correctly in Access.  </p>\n\n<pre><code>SELECT\n    a.account,\n    a.time,\n    a.value\nFROM\n    tablename AS a INNER JOIN [\n        SELECT\n            account,\n            Max(time) AS MaxOftime\n        FROM\n            tablename\n    GROUP BY\n        account\n    ]. AS b\n    ON\n        (a.time = b.MaxOftime)\n        AND (a.account = b.account)\n;\n</code></pre>\n'}, {'answer_id': 447895, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 0, 'selected': False, 'text': "<p>I'm trying to find the latest date in a group using the Access 2003 query builder, and ran into the same problem trying to use LAST for a date field. But it looks like using MAX finds the lates date.</p>\n"}, {'answer_id': 17624209, 'author': 'pghcpa', 'author_id': 149572, 'author_profile': 'https://Stackoverflow.com/users/149572', 'pm_score': 2, 'selected': False, 'text': '<p>After literally years of searching I finally found the answer at the link below #3.  The sub-queries above will work, but are very slow -- debilitatingly slow for my purposes.</p>\n\n<p>The more popular answer is a tri-level query: 1st level finds the max, 2nd level gets the field values based on the 1st query. The result is then joined in as a table to the main query.  Fast but complicated and time-consuming to code/maintain.</p>\n\n<p>This link works, still runs pretty fast and is a lot less work to code/maintain.  Thanks to the authors of this site.</p>\n\n<p><a href="http://access.mvps.org/access/queries/qry0020.htm" rel="nofollow">http://access.mvps.org/access/queries/qry0020.htm</a></p>\n'}]
['https://Stackoverflow.com/questions/47413', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/4873/']