-1

In Access I have a query in which I inner join on another query, the max query. The max query is just a query with a max function on the timestamp field and with that query I only retrieve the latest records of a data set.

Can you also save a query in sql (up till now I only know that you can save views) and use that as a max query or should you a subquery to inner join on?

Thanks in advance,

Michiel

Michiel Soede
  • 45
  • 6

1 Answers1

0

You can do this all in one:

SELECT * 
FROM table 
WHERE year=2015 
  AND birthday=(
      SELECT MAX(birthday) 
      FROM table 
      WHERE year=2015
      );
  • Thanks for your answers! But not sure how to translate it into a query which also joins on other fields. So in other words if birthday would be my timestamp of data being stored by a user, the data record should show up joining country, region and accountitem. So I need have every max time stamp of a country, region and account combination. – Michiel Soede Dec 09 '15 at 18:39
  • Please amend the question so that it is understandable what you have and what you want to do. Read the help to find out how to ask a good question and why it is important to take your time in asking the question. Oh, and this is no free support service - its a place where programming problems are asked and discussed for the greater benefit of all. So try to make your question as general as possible so others can profit from it too. –  Dec 10 '15 at 00:13
  • Okay true point. For example I have the following fields: YEAR / Month Region / Unit / Version / Customer / Program / Value / ToolCode / Timestamp in a table. How can I get the most recent value (max timestamp) of each unique record. Unique means combination of Year, Month, Region, Unit, Version, Customer, Program, Toolcode! – Michiel Soede Dec 10 '15 at 11:04
  • see also: http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column –  Dec 10 '15 at 14:32
  • @michiel: please comment if this doesn't help you - and mark the answer as correct is it did. –  Dec 11 '15 at 17:00
  • I am still not fully sure. I understand the code in the link you sent but what I am not sure about is that in the join you see Yourtable A and Yourtable B: SELECT a.id, a.rev, a.contents FROM YourTable a INNER JOIN ( SELECT id, MAX(rev) rev FROM YourTable GROUP BY id ) b ON a.id = b.id AND a.rev = b.rev. There is only one table. So what is the B referring to? Thanks again – Michiel Soede Dec 13 '15 at 12:35
  • You dont want to|can't aggregate(sum, average etc.) the comment field. But to find the record with the MAX(rev) you have to group your table. So: you group it by MAX(rev) and ID and then bind a normal (ungrouped) instance of the table to the MAX and ID. So the QUERY shows you every Recordset that has an ID and REV combination that exists in the grouped table (where rev is MAX(rev). –  Dec 13 '15 at 13:32
  • Understand what you're saying but what does the b refer to in this query? Is that the grouped table? And if so how does sql recognize the name b (where there is only one table name being the one containing the data)? Very much appreciate your reaction – Michiel Soede Dec 14 '15 at 06:59
  • b is an alias (like a). The while inner SELECTion resolves to a bunch of recordsets to which you now can refer as b. So b.id means the id fields in that subquery. –  Dec 14 '15 at 17:40
  • Clear but can I literally use b (so has B become an actual reference that can be used) or should I use the [originaltablename].[rev] also in the subquery? – Michiel Soede Dec 16 '15 at 19:26
  • Perhaps a tutorial would be in order? B is the alias of the inner select. You have to use it to access the fields of that query. Otherwise you access the fields of another instance of the table. But normally you wouldnt need to use b, because its only there to provide you with the ids of the records with the biggest rev. –  Dec 17 '15 at 20:38
  • thanks! I will definitely take a tutorial! But for this purpose the above instructions should be sufficient – Michiel Soede Dec 19 '15 at 08:05