CODEWITHSUNDEEP
oracle-database
John Smith
John Smith

Reputation: 23

Problems using GROUP BY and ORDER BY in SQL Oracle

So I've been instructed to write a SQL query to display the manager number and the salary of the lowest paid employee for that manager(2 columns within a database I'm working in). I must also exclude any groups where the minimum salary is less than or equal to $6,000. Sort the output in descending order of salary. Now I've successfully displayed the data however, I cannot seem to order it in descending order which is what brought me here. Currently my code is as follows:

Code Snippet #1

SELECT manager_id AS "Manager ID",
MIN(salary) AS "Lowest Paid Salary"
FROM employees
WHERE manager_id IS NOT NULL
AND salary > 6000 
GROUP BY manager_id;

Which gives me the following output: Code Snippet #2 

Manager ID Lowest Paid Salary
---------- ------------------
       100               6500
       147               6200
       205               8300
       108               6900
       148               6100
       149               6200
       102               9000
       101               6500
       145               7000
       146               7000

However, I need these values ordered from Largest -> Smallest salary. I attempted to add a GROUP BY salary DESC; At the end of the statement which would give me the following error: ORA-00979: not a GROUP BY expression

Upon some research I found that the two (GROUP BY and ORDER BY) cannot be used together in the same query as I did not include both manager_id and salary within the GROUP BY clause. So I attempted that with the following code:

SELECT manager_id AS "Manager ID",
MIN(salary) AS "Lowest Paid Salary"
FROM employees
WHERE manager_id IS NOT NULL
AND salary > 6000 
GROUP BY manager_id, salary
ORDER BY salary DESC;

Which gives me a list of all the salaries for each manager_id as well as duplicates. This is not what I am looking for.

I apologize for the excess of reading, I just want to make sure you know what I've tried and what I am attempting to accomplish.

So I need to know a way I can either use both the GROUP BY and ORDER BY in the same query to display the Code snippet #2 but have it ordered. Or another way to order this data in the same format.

Thanks in advance for any help/advice.

Upvotes: 1

Views: 6601

Answers (3)

Syed Osama Maruf
Syed Osama Maruf

Reputation: 1935

Try: order by manager_id ,salary DESC and remove the group by clause for example SELECT * FROM Customers ORDER BY CustomerID DESC,CustomerName; This statement will arrange the results according to customer id in desc order.

Upvotes: 0

Spock
Spock

Reputation: 4910

You need to order by the aggregate function. ... Try this

SELECT manager_id AS "Manager ID",
MIN(salary) AS "Lowest Paid Salary"
FROM employees
WHERE manager_id IS NOT NULL
AND salary > 6000 
GROUP BY manager_id
ORDER BY min(salary) DESC;

Upvotes: 4

Y.S
Y.S

Reputation: 1862

You can either sort by the column number or column name

SELECT manager_id AS "Manager ID",
MIN(salary) AS "Lowest Paid Salary"
FROM #employees
WHERE manager_id IS NOT NULL
AND salary > 6000 
GROUP BY manager_id
order by 2 desc


SELECT manager_id AS "Manager ID",
MIN(salary) AS "Lowest Paid Salary"
FROM #employees
WHERE manager_id IS NOT NULL
AND salary > 6000 
GROUP BY manager_id
order by "Lowest Paid Salary" desc

Upvotes: 6

Related Questions