puritm

 Profile Answers by puritm 

• Oct 5th, 2010

 

  1  SELECT * FROM dob
  2  WHERE TO_CHAR(dob,'DD') IN
  3  (
  4  SELECT TO_CHAR(dob,'DD') --,COUNT(name)
  5  FROM dob
  6  GROUP BY TO_CHAR(dob,'DD')
  7  HAVING COUNT(TO_CHAR(dob,'DD')) > 1
  8* )
SQL> /

NAME       DOB
---------- -----------
John       28-JUN-1985
Natalia    25-APR-1984
Kate       28-JUN-1984
robert     25-APR-1984

yagneswarasankar

 Profile Answers by yagneswarasankar 

• Nov 15th, 2010

 

This can be resolved as we have only two rows that are having the same birth date. But dont know how to do it in SQL when we have more number of people with same birth date.

select a.name||','||b.name  from birthdate a,birthdate b
where a.name < b.name and to_char(a.birthday,'DD/MM') = to_char(b.birthday,'DD/MM');

puritm

 Profile Answers by puritm 

• Nov 16th, 2010

 

This query will retrieve the correct record if more pepole having same birth date.

SELECT * FROM dob
WHERE TO_CHAR(dob,'DDMM') IN
(
SELECT TO_CHAR(dob,'DDMM') -- COUNT(name)
FROM dob
GROUP BY TO_CHAR(dob,'DDMM')
HAVING COUNT(TO_CHAR(dob,'DDMM')) > 1
);

NAME       DOB
---------- -----------
John       28-JUN-1985
Natalia    25-APR-1984
Kate       28-JUN-1984
robert     25-APR-1984
Sam        25-APR-1995
Jon        25-APR-1989
Man        25-APR-2000

barbie_sashi

 Profile Answers by barbie_sashi 

• Dec 15th, 2010

 

The following query retuns the names of all people having birthday(including the year) on the same date. If only day and month need to be considered, we need to use to_char(birthdate,'DD-MM') instead of birthdate

select * from bday_tab where (birthdate) in (select birthdate from bday_tab group by birthdate having count(birthdate) > 1) order by birthdate

Result:

William	07-JUN-94
Susan	07-JUN-94
Hermann	07-JUN-94
Shelley	07-JUN-94
Alberto	10-MAR-97
Lindsey	10-MAR-97
Laura	20-AUG-97
Peter	20-AUG-97
Winston	24-JAN-98
Tayler	24-JAN-98

dineshlade_G9

 Profile Answers by dineshlade_G9 

• Dec 16th, 2010

 

SELECT * FROM employees e
 WHERE convert(varchar,BirthDate, 103) IN
 (
 SELECT convert(varchar,ee.BirthDate, 103)
 FROM employees ee
 GROUP BY convert(varchar,ee.BirthDate, 103)
 HAVING COUNT(convert(varchar,ee.BirthDate, 103)) > 1)

binita007

 Profile Answers by binita007 

• Dec 17th, 2010

 

CREATE table birthday(name varchar(20),DOA Date);
insert into birthday values('John',to_date('28-06-1985','dd-mm-yyyy'));
insert into birthday values('Peter',to_date('27-08-1987','dd-mm-yyyy'));
insert into birthday values('Tom',to_date('13-08-1986','dd-mm-yyyy'));
insert into birthday values('Nick',to_date('29-04-1987','dd-mm-yyyy'));
 insert into birthday values('Natalia',to_date('25-04-1984','dd-mm-yyyy'));
insert into birthday values('Kate',to_date('28-06-1984','dd-mm-yyyy'));
insert into birthday values('Wilson',to_date('11-11-1987','dd-mm-yyyy'));
 insert into birthday values('Krish',to_date('31-07-1987','dd-mm-yyyy'));
insert into birthday values('Robert',to_date('25-04-1984','dd-mm-yyyy'));

SELECT*
FROM birthday
where TO_CHAR(doa,'DDMM')IN(SELECT TO_CHAR(doa,'DDMM')--COUNT(name)
from birthday
GROUP BY TO_CHAR(doa,'DDMM')
HAVING COUNT(TO_CHAR(doa,'DDMM'))>1);

Result :

NAME                 DOA
----------              -------------
Natalia              25-APR-84
Robert               25-APR-84
John                  28-JUN-85
Kate                   28-JUN-84

panwar_5

 Profile Answers by panwar_5 

• Jan 7th, 2011

 

This can be done via self join

Select a.name,b.dob from datebirth a,datebirth b

where a.dob=b.dob

Narendra Tomar

 Profile Answers by Narendra Tomar 

• Mar 7th, 2011

 

Try this:

SELECT to_char (dob, 'DD-MON-YY') date_of_birth,  XMLAGG( XMLELEMENT  (E, Name || ',')).EXTRACT ('//text()').GETSTRINGVAL() names
FROM bday GROUP BY TO_CHAR (dob, 'DD-MON-YY')

Tested in oracle 10g.

Balakrishna12207

 Profile Answers by Balakrishna12207 

• Mar 8th, 2011

 

Select Name from Tab_NB where To_Char(Birthday,'DD') in (select distinct(to_char(Birthday,'DD')) from Tab_NB);


Using Subquery, we are getting distinct birthdates from table, that dates are compared to where condition dates, is there any matching dates that are displayed.


Balakrishna. D

Prabakaran

• Jul 27th, 2011

 

select name from btab where bday in (select distinct bday from btab)

Jonno

• Feb 1st, 2018

 

Code
--20180201 JL


SELECT a.name, a.DOB FROM birthday a


        INNER JOIN birthday b ON DAY(a.DOB) = DAY(b.DOB) AND MONTH(a.DOB) = MONTH(b.DOB)


GROUP BY a.name, a.DOB


HAVING COUNT(1) > 1


ORDER BY a.DOB