CODEWITHSUNDEEP
sqldatabaseoracle-database
hank99
hank99

Reputation: 185

Oracle Database sql query. Having?

Show the name of all the employees who were hired on the day of the week on which the highest number of employees were hired.

Table:

Steven  06/17/1987
Neena   09/21/1989
Lex     01/13/1993
Alex    01/03/1990
Bruce   05/21/1991
Diana   02/07/1999
Kevin   11/16/1999
Trenna  10/17/1995
Curtis  01/29/1997
Randall 03/15/1998
Peter   07/09/1998
Eleni   01/29/2000
Ellen   05/11/1996
Jonath  03/24/1998
Kimber  05/24/1999
Jenni   09/17/1987
Michael 02/17/1996
Pat     08/17/1997
Shelley 06/07/1994
William 06/07/1994

What I have so far.

SELECT FIRST_NAME, to_char(hire_date,'d') AS DOW FROM EMPLOYEES;

Steven  4
Neena   5
Lex     4
Alex    4
Bruce   3
Diana   1
Kevin   3
Trenna  3
Curtis  4
Randall 1
Peter   5
Eleni   7
Ellen   7
Jonath  3
Kimbe   2
Jenni   5
Michael 7
Pat     1
Shelley 3
William 3

Sunday is 1, monday is 2, ... so on...

Now i need to select the one with the max repeating number.

Which by looking at the table we will know it's 3 (tuesday). I know i will need to use a subquery to get it, is it having?

Upvotes: 1

Views: 120

Answers (3)

Kirill Leontev
Kirill Leontev

Reputation: 10931

select * 
  from employees
 where to_char(hire_date, 'd') = (
     select max(to_char(hire_date, 'd')) keep (dense_rank last order by count(*))
       from employees 
      group by to_char(hire_date, 'd')
 );

SQLFiddle

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269683

I would be inclined to use analytic functions for this:

select e.*
from (SELECT to_char(hire_date, 'd') AS DOW, count(*) as cnt,
             row_number() over (order by count(*) desc) as seqnum
      FROM EMPLOYEES
     ) dow join
     EMPLOYEEs e
     on dow.DOW = to_char(e.hire_date, 'd') and seqnum = 1;

Upvotes: 4

lc.
lc.

Reputation: 116458

One way, extending your query above (SQL Fiddle Example):

SELECT FIRST_NAME, to_char("hire_date", 'd') AS DOW
FROM EMPLOYEES
WHERE to_char("hire_date", 'd') =
  (
      SELECT b.DOW 
      FROM
      (
        select a.*, ROWNUM rnum 
        from (
          SELECT to_char("hire_date", 'd') AS DOW, COUNT(1) AS cnt
          FROM EMPLOYEES 
          GROUP BY to_char("hire_date", 'd')
          ORDER BY cnt DESC
        ) a
        where rownum = 1
      ) b
  )

Upvotes: 1

Related Questions