Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2009
    Posts
    3

    Unanswered: Using "Max" over multiple tables

    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

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Suresh, The query in the Answer is using 2 table. One directly and one in a derived (nested) table. The fact that the table in the derived table happens to be the same table is totally immaterial to the query. You can just substitute your second table in the derived table and you should be fine.

    Code:
    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 your-table
    GROUP BY report_name) maxresults
    WHERE rh.report_name = maxresults.report_name
    AND rh.report_run_date= maxresults.maxdate;

  3. #3
    Join Date
    Oct 2009
    Posts
    3
    Hi stealth, Thanks for your reply. I cannot replace my table as you mentioned because my second table doesnt have the column report_name. It has only report_id. This report_id column is referred in the first table.

    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

    Thank you,
    Regards
    Suresh

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try this:
    Code:
    WITH cte as 
    (
    SELECT 
    	T1.user_name, T1.report_name, T2.report_run_date,
    	ROW_NUMBER() OVER (PARTITION BY T1.report_name 
    			ORDER BY T2.report_run_date DESC) as RowNum
    FROM FirstTable as T1
    	INNER JOIN SecondTable as T2 On 
    		T1.report_id = T2.report_id
    )
    SELECT user_name, report_name, report_run_date
    FROM cte
    WHERE RowNum = 1
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Suresh,
    Doesn't the query Stealth gave you work with replacing name with id?
    Dave

  6. #6
    Join Date
    Oct 2009
    Posts
    3
    Thanks a lot Wim for your reply.The query is working fine.
    Dave - If i replace the name with ID, the query is not returning the expected results. There are some duplicates in the result. Thanks a lot for your reply.

    Regards
    Suresh

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •