retrieving multiply records from mysql database

[liomon41]

Hey you guys ...  i have table called info and it contains records of students ID, FirstName, LastName and DOB.... And there are some duplicates records in the DOB..

 

***Please check attached word file...***

 

my question how do you query the database to display duplicate records first as shown in the output before displaying single records... thanks you guys...

 

18552_.doc

[liomon41]

table info

 

ID firstName  lastName         DOB

1 Lionel         Johnson         1990

2 Vincent         Williams         1990

3 Gary                 Coleman         1985

4 John          Liverpool         1903

5 Lilly               White         1903

 

 

##this is how i want the output to look like.....

output

 

1990

      Lionel Johnson

      Vincent Williams

 

1903

      John Liverpool

      Lilly White

 

1985

      Gary Coleman

 

 

 

[Illusion]

try this...

Select a.DOB,a.firstname,a.lastname,b.cnt from info a inner join (select DOB ,Count(*) as cnt from info group by DOB ) b on a.DOB=b.DOB order by b.cnt desc

[cpd]

I don't think that achieves what he's trying to do. It'll just come back with count's.

 

I think you need to look at pulling the data with the result set ordered by the DoB. Then cycle through your result set creating a new array which has a structure of.

 

array(
    '1990' => array(
        0 => array(
            'First name',
            'Last name'
        ),
        1 => array(...),
    '1991' => array(...)
)

 

When selecting you return entire rows with true values or NULL dependant on how the whole table and select statement is set-up. You can't get it to chuck out 1990 and then all the corresponding rows.

[Illusion]

I don't think that achieves what he's trying to do. It'll just come back with count's.

It fetches the records in the order he  wanted........... duplicates listed first and then single ones  and its up to him how implements the  display part. If he wants pure sql solution then he could just use group_concat.

Select a.DOB,group_concat ( (a.firstname+ ' '+a.lastname) separator ',' ),b.cnt from info a inner join (select DOB ,Count(*) as cnt from info group by DOB ) b on a.DOB=b.DOB group by a.DOB order by b.cnt desc

 

or

Select DOB,group_concat ( (firstname+ ' '+lastname) separator ',' ),count(id) as cnt from info  group by  DOB order by cnt desc