0

Dear ladies and gentlemen, Unfortunately I'm totally new to sql programming, so I apologize for any definite errors or lack of knowledge - For my Access database i have a table "report" with "reportnumber" as id (there can be multiple versions of each report with different dates) as well as the "reportdate".

I now want my query to display ONLY the last "reportnumber"s with the latest "reportdate"s.

I've tried the following sql code I have found and adapted it but it only gives me error messages (syntax in declaration WITH OWNER ACCESS OPTION?):

SELECT reportnumber, MAX(reportdate) AS LatestDate FROM report 
GROUP BY reportnumber 
WITH numbered AS ( SELECT reportnumber, reportdate
ROW_NUMBER() OVER (PARTITION BY reportnumber 
ORDER BY reportdate DESC) AS rownum FROM report ) 
SELECT reportnumber, reportdate  
FROM numbered WHERE rownum = 1;

I'd really appreciate your help. Thank you very much and kind regards,

Daniel Funk

2 Answers 2

0

Assuming I understand what you are asking correctly try a correlated subquery.

I'm assuming here that you have other columns you need to pull in the row that match the latest reportdate for a given reportnumber so I'm adding an additional column.

SELECT reportnumber, reportdate, reportinfo
FROM report
WHERE reportdate = (SELECT max(reportdate) FROM report r2
                     WHERE r2.reportnumber = report.reportnumber)

If on the other hand you only need a single reportnumber with the latest reportdate then a simple GROUP BY will work.

SELECT reportnumber, MAX(reportdate)
FROM report
GROUP BY reportnumber
4
  • Dear Sir, thank you very much for your help - I managed to apply the second part of your suggested query, it now looks like this (icsrsnumb is my report number): SELECT icsrsreport.icsrsnumb, Max(icsrsreport.messagedate) AS Maxvonmessagedate FROM icsrsreport GROUP BY icsrsreport.icsrsnumb; Unfortunately when I try to apply the sql statement to implement other fields as well I fail, access keeps asking me to enter a value for messagedate for the query. How could I apply this right? Thanks Nov 2, 2014 at 16:12
  • Don't forget that any additional columns either need to be part of the group by or have an aggregate around them. If neither of those work for you then you will need to use the first query. I'm not sure how to build it into the viewer but when I pasted it into the SQL view it worked fine. Nov 2, 2014 at 17:05
  • Dear Sir, after countless hours of trying to get this to work I have now finally succeeded, thanks to the first query solution you have provided! Thank you very much, you are indeed a genius! I have tried with a lot of other complicated queries but sometimes the easier way is the key to the solution! Nov 5, 2014 at 13:52
  • No problem. Please go ahead and mark the answer correct in order to close the question :) Nov 5, 2014 at 16:08
0

You can accomplish this with a simple group by clause.

Edit: to get the additional row values for the aggregated column in a group by clause, you can use a subquery. Since I'm not sure what your additional columns are, I just added a column called title.

Here's the example, and I will clarify what is happening below.

http://sqlfiddle.com/#!2/61c3a/15

drop table if exists report;
create table report
(reportnumber mediumint,
 reportdate date,
 title varchar(60));

insert into report
values
(1, '2014-04-01', 'mysql tutorial, part 1'), 
(1, '2014-04-04', 'mysql tutorial, part 2'), 
(2, '2014-11-05', 'hbase tutorial, part 1'), 
(2, '2014-11-08', 'hbase tutorial, part 2');

select r.*, r2.title
from (
  select reportnumber, max(reportdate) as maxdate
  from report
  group by reportnumber
) r
inner join report r2
on r.reportnumber = r2.reportnumber
and r.maxdate = r2.reportdate;

The results of the subquery (i.e. the query in the first from clause above), are essentially being treated as a table. With that in mind, we can join our regular table, report, to the aggregated results from the subquery, r, to pull the correct values for the other columns (in this case, the title column).

That may be a little hard to understand if you are new to sql, but read over it a few times and it will start to make sense. Good luck!

5
  • Thanks, I have tried this and it now only gives me 1 listing of the "reportnumber" and "messagedate" with the latest date - I need to have all reports with their specified number who have the latest dates - e.g. I have reportnumber 1 dated 1.April and reportnumber 1 dated 4.April, as well as reportnumber 2 with 5.November and reportnumber 2 with 8. November I want the query to display only reportnumber 1 from 4. April and reportnumber 2 from 8. November. Nov 1, 2014 at 23:59
  • I created a new fiddle. Is this what you are looking for, sqlfiddle.com/#!2/e29581/3 ? If so, let me know and I will update my answer
    – foxygen
    Nov 2, 2014 at 0:09
  • Thanks this works out, but unfortunately not if I want to display other columns too according to the query filter - how can I put other columns in there to be displayed as you have given in the fiddle query? Nov 2, 2014 at 21:19
  • @FunkmannDanny - see my edit. You should be able use this type of query to get the results you want
    – foxygen
    Nov 2, 2014 at 21:54
  • Thanks I've tried this, unfortunately access asks me to type in the parameter value for each added column (e.g. in you given example case "title") prior to executing the query...Is there any other way or can I send you the accdb file to see for yourself? Nov 4, 2014 at 23:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.