I have a query that displays employee information for all employees, who were hired on the day of the week where the most employees were hired. In my test CASE this day is Tuesday.
Although I got the query to work it looks clunky and awkward and I can't help thinking there is a better way to achieve the same output in a more succinct way using rank/ dense_rank or maybe greatest?
I was hoping someone can provide me with a solution so the query is easier to read and understand.
Below is my test CASE and output that I would like to have revamped. Thanks in advance to all who answer and your expertise.
CREATE TABLE employees (employee_id, first_name, last_name, hire_date) AS
SELECT 1, 'Lisa', 'Saladino', DATE '2001-04-03' FROM DUAL UNION ALL
SELECT 2, 'Abby', 'Abbott', DATE '2001-04-04' FROM DUAL UNION ALL
SELECT 3, 'Beth', 'Cooper', DATE '2001-04-05' FROM DUAL UNION ALL
SELECT 4, 'Carol', 'Orr', DATE '2001-04-06' FROM DUAL UNION ALL
SELECT 5, 'Vicky', 'Palazzo', DATE '2001-04-07' FROM DUAL UNION ALL
SELECT 6, 'Cheryl', 'Ford', DATE '2001-04-08' FROM DUAL UNION ALL
SELECT 7, 'Leslee', 'Altman', DATE '2001-04-10' FROM DUAL UNION ALL
SELECT 8, 'Jill', 'Coralnick', DATE '2001-04-11' FROM DUAL UNION ALL
SELECT 9, 'Faith', 'Aaron', DATE '2001-04-17' FROM DUAL;
select *
from employees
where TO_CHAR(HIRE_DATE,'DAY') = (select DAY
from (SELECT TO_CHAR(HIRE_DATE,'DAY') DAY ,count(*) cnt FROM EMPLOYEES GROUP BY TO_CHAR(HIRE_DATE,'DAY'))
where cnt = (select max(cnt) from (SELECT TO_CHAR(HIRE_DATE,'DAY') DAY ,count(*) cnt FROM EMPLOYEES GROUP BY TO_CHAR(HIRE_DATE,'DAY'))));
EMPLOYEE_ID FIRST_NAME LAST_NAME HIRE_DATE
1 Lisa Saladino 03-APR-01
7 Leslee Altman 10-APR-01
9 Faith Aaron 17-APR-01