Re: SQL guestion finding duplicates

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 Received on Mon Jul 11 2005 - 09:57:44 CDT