Ranking?

[brown2005]

Hi i have a members table with these two fields;

firstname
sex

what i want to do is create a ranking table based on these as below.

[b] All Female Male[/b]
1 Paul Rachel Paul
2 Rachel Elizabeth Richard
3 Elizabeth Tina Matthew
[b]

I can create single lists, but I dont know if it can be done in one query...

Any ideas please?

[samshel]

Hello,

i think i did not get you properly !

can u please elborate more on your query ?

[brown2005]

i want a query that says the following:-

most popular female first name
most popular male first name
most popular first name (either male or female)

[shoz]

You haven't made it clear how you determine the popularity of an individual, but you can do the sorting in PHP using something similar the code below.

Btw, in your first post you give the impression that you want to have a list showing the females and males in an order based on their rank/popularity. While, in your second post you make it seem as though you only want the list to contain one entry for most popular male and most popular female.
[code]
SELECT name, sex FROM table ORDER BY rank DESC
$all = array();
$females = array();
$males = array();
$other = array();
while ($row = mysql_fetch_assoc($result))
{
    $all[] = $row['name'];
    if ($row['sex'] == 'f')
    {
         $females[] = $row['name'];
    }
    elseif ($row['sex'] == 'm')
    {
        $males[] = $row['name'];
    }
    else
    {
        $other[] = $row['name'];
    }
}
print_r($all);
print_r($males);
print_r($females);
print_r($other);
[/code]