Using "Max" over multiple tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Using "Max" over multiple tables

Author

Topic

suresram
Starting Member

1 Post

Posted - 2009-10-16 : 19:02:31

I read the following question and answer in one of the forum.

Question:

I'm trying to pull some info out of a table. To simplify, let's say the table (report_history) has 4 columns:

user_name, report_job_id, report_name, report_run_date.

Each time a report is run in Oracle, a record is written to this table noting the above info. What I am trying to do is pull from this table when the last time each distinct report was run and who ran it last.

My initial query:

SELECT report_name, max(report_run_date)
FROM report_history
GROUP BY report_name

runs fine. However, it does not provide the name of the user who ran the report.

Adding user_name to both the select list and to the group by clause returns multiple lines for each report; the results show the last time each person ran each report in question. (i.e. User1 ran Report 1 on 01-JUL-03, User2 ran Report1 on 01-AUG-03). I don't want that....I just want to know who ran a particular report the last time it was run.

Any suggestions?

Answer: This is where things get a bit complicated. The SQL statement below will return the results that you want:

SELECT rh.user_name, rh.report_name, rh.report_run_date
FROM report_history rh,
(SELECT max(report_run_date) as maxdate, report_name
FROM report_history
GROUP BY report_name) maxresults
WHERE rh.report_name = maxresults.report_name
AND rh.report_run_date= maxresults.maxdate;

I am facing a problem similar to this except that the report_run_date is in another table.
For Example.
My first table has the following columns
user_name, report_job_id, report_name, report_id
My second table has the follwoing column
report_id, report_run_date

I need to do the same operation mentioned above, i.e to get the last time each distinct report was run and who ran it last.

Please help me to solve this problem. Thanks in advance.

Regards
Suresh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-20 : 12:50:38

[code]
SELECT t1.user_name, t1.report_job_id, t1.report_name,MAX(t2.report_run_date)
FROM table1 t1
JOIN table2 t2
ON t2.report_id=t1.report_id
GROUP BY t1.user_name, t1.report_job_id, t1.report_name
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources