Hello,

I have a SQL question. Consider the following table...

----------------------
| Employee |
----------------------
| Name | Birthdate |
----------------------
| Joe | 1 July |
| Sue | 10 Aug |
| Mark | 15 Sep |
| Jane | 10 Aug |
----------------------

I'd like to find out all the employees who share the same birthday. I
was thinking initially that if I used SQL HAVING that would work... but
then I thought (from what I understand about the command) HAVING only
works on arithmetic operators. Also if I set up a condition with
WHERE, wouldn't that imply some previous knowledge of the birthdays?

My development background screams doing some kind of string compare or
regular expression matching but that could either get complicated or
perform poorly if the employee table had thousands of rows.

At any rate, how could I write a SQL statement to return all the names
(and birthdates) of employees with identical birthdays?

Thanks!!!

"Adam Sandler" <corn29@excite.com> wrote in
news:1121092714.729379.239250@g47g2000cwa.googlegr oups.com:

> Hello,
>
> I have a SQL question. Consider the following table...
>
> ----------------------
>| Employee |
> ----------------------
>| Name | Birthdate |
> ----------------------
>| Joe | 1 July |
>| Sue | 10 Aug |
>| Mark | 15 Sep |
>| Jane | 10 Aug |
> ----------------------
>
> I'd like to find out all the employees who share the same birthday. I
> was thinking initially that if I used SQL HAVING that would work... but
> then I thought (from what I understand about the command) HAVING only
> works on arithmetic operators. Also if I set up a condition with
> WHERE, wouldn't that imply some previous knowledge of the birthdays?
>
> My development background screams doing some kind of string compare or
> regular expression matching but that could either get complicated or
> perform poorly if the employee table had thousands of rows.
>
> At any rate, how could I write a SQL statement to return all the names
> (and birthdates) of employees with identical birthdays?
>
> Thanks!!!
>
>

HAVING COUNT(*) > 1

Adam Sandler wrote:
> Hello,
>
> I have a SQL question. Consider the following table...
>
> ----------------------
> | Employee |
> ----------------------
> | Name | Birthdate |
> ----------------------
> | Joe | 1 July |
> | Sue | 10 Aug |
> | Mark | 15 Sep |
> | Jane | 10 Aug |
> ----------------------
>
> I'd like to find out all the employees who share the same birthday. I
> was thinking initially that if I used SQL HAVING that would work... but
> then I thought (from what I understand about the command) HAVING only
> works on arithmetic operators. Also if I set up a condition with
> WHERE, wouldn't that imply some previous knowledge of the birthdays?
>
> My development background screams doing some kind of string compare or
> regular expression matching but that could either get complicated or
> perform poorly if the employee table had thousands of rows.
>
> At any rate, how could I write a SQL statement to return all the names
> (and birthdates) of employees with identical birthdays?
>
> Thanks!!!
>

Returns all employees sharing particular birth date:
select * from employee where birthdate='PARTICULAR_BIRTH_DATE'

Returns all employess where at least 2 employees share identical birth date:
select name,birthdate
from ( select count(*) over(partition by birthdate) cnt,name,birthdate
from employees )
where cnt > 1

In general take a look on analytical functions

Best regards

Maxim