This syntax is optimized for Oracle and may not work for other database technologies. This SQL SELECT statement will summarize the total orders for each customer and then return the customer with the highest total orders.

WHERE query1.total_amt = quer圓.highest_amt (SELECT MAX(query2.total_amt) AS highest_amt SELECT query1.*įROM (SELECT customer, SUM(orders.amount) AS total_amt I need a query that will return the customer who has ordered the highest total amount.Īnswer: The following SQL should return the customer with the highest total amount in the orders table. I have a table in Oracle called orders which has the following fields: order_no, customer, and amount. This allows us to retrieve the report_name, max(report_run_date) as well as the user_name. So we've joined the report_name and report_run_date fields between the tables called rh and maxresults. Now, that we've created this select statement within our FROM clause, Oracle will let us join these results against our original report_history table.

We've aliased the max(report_run_date) as maxdate and we've aliased the entire result set as maxresults. The second is a select statement: (SELECT MAX(report_run_date) AS maxdate, report_name The first is the table called report_history (aliased as rh). Second, we've included two components in our FROM clause. Let's take a few moments to explain what we've done.įirst, we've aliased the first instance of the report_history table as rh. WHERE rh.report_name = maxresults.report_nameĪND rh.report_run_date= maxresults.maxdate (SELECT MAX(report_run_date) AS maxdate, report_name The SQL SELECT statement below will return the results that you want: SELECT rh.user_name, rh.report_name, rh.report_run_date I don't want that.I just want to know who ran a particular report the last time it was run.Īnswer: This is where things get a bit complicated.

User1 ran Report 1 on 01-JUL-03, User2 ran Report1 on 01-AUG-03). However, it does not provide the name of the user who ran the report.Īdding 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. My initial query: SELECT report_name, MAX(report_run_date)

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. To simplify, let's say the table (report_history) has 4 columns: user_name, report_job_id, report_name, and report_run_date.Įach time a report is run in Oracle, a record is written to this table noting the above info. Question: I'm trying to pull some info out of a table.

Use sql on mac