You need to make sure that you are refining your query results to only return employees from department 30. In your case, you must have 2 employees (one in department 30 and another in department 80) that have this same salary. I've tried the following query, but it returns the result from both department 30 and 80: SELECT *Īnswer: The SQL SELECT statement that you have written will first determine the maximum salary for department 30, but then you select all employees that have this salary.
#Study sql on mac full
Question: I'm trying to get the employee with the maximum salary from department 30, but I need to display the employee's full information. 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 order count. WHERE query1.order_count = quer圓.highest_count
GROUP BY cust_order.Customer_id) query2) quer圓 (SELECT max(query2.order_count) AS highest_count SELECT query1.*įROM (SELECT Customer_id, Count(*) AS order_count Can you help me please?Īnswer: The following SQL SELECT statement should return the customer with the highest order count in the cust_order table. This gives me the max Count, But, I can't get the CUSTOMER_ID. I would like to find the customer_id, who has Highest order count. I have a table in Oracle called cust_order which has the following fields: OrderNo, Customer_id, Order_Date, and Amount. WHERE Score = (SELECT MAX(Score) FROM Scoring) What I want to get is the highest score from the table and the name of the player.Īnswer: The following SQL SELECT statement should work: SELECT Name, Score I've got a table named Scoring with two fields - Name and Score. Question: I'm trying to retrieve some info from an Oracle database. 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.