Resolved: Query regarding Exercise 1
Write a query that upon execution, assigns a row number to all managers we have information for in the "employees" database (regardless of their department). - So my understanding for this is that we need need distinct managers from the dept_manager table. So, lets say there is an employee that was manager in department d001 and department d002 (for different time period), we need that person counted as 1 count in the result set.
In the dept_manager table there was no such employee(which has 2 department member assigned to it during different times). So, i executed the following statement:
INSERT INTO dept_manager VALUES('110022', 'd002', '1991-10-02', '1992-10-02');
Now dept_manager table has 2 records for employee '110022' (one for department d001 and other for department d002)
Then, i ran following two statements to get the desired resultset:
SELECT DISTINCT emp_no,
ROW_NUMBER() OVER (ORDER BY emp_no) AS row_num
FROM dept_manager;
SELECT emp_no, dept_no,
ROW_NUMBER() OVER (ORDER BY emp_no) AS row_num
FROM dept_manager;
Now, both of them is counting employee '110022' as 2 rows with rownum 1 and 2.
How can i change the query so it counts it as 1 (i.e it doesnt care which department the employee is manager and counts it as 1 row)?
Hi Tanika!
Thanks for reaching out.
If you want each manager to be counted only once regardless of the departments they've managed, you should use the `DISTINCT` keyword in conjunction with the `ROW_NUMBER()` window function. However, since you can't directly use `ROW_NUMBER()` on a distinct set in a single step, you'll need to use a subquery or a Common Table Expression (CTE).
Here's how you can achieve this using a subquery:
SELECT emp_no, ROW_NUMBER() OVER (ORDER BY emp_no) AS row_numFROM ( SELECT DISTINCT emp_no FROM dept_manager) AS distinct_managers;
This approach first gets the distinct `emp_no` from the `dept_manager` table in the subquery. Then, the outer query assigns a row number to each of these distinct managers.
Hope this helps.
Best,
Tsvetelin
Thank you! Thats helpful