Help needed with simple query

[jasongr]

Hi,

I need a quick help with a small query.

I have a table that holds association between people and photos like so:

create table people_in_photo (
`Image_ID` int unsigned not null,
`Individual_ID` int unsigned not null,
PRIMARY KEY (`Image_ID`, `Individual_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


I need to run a query that will return the mapping of the people in the photos ordered by associations.
For example, if person 5 appears in 10 different photos, and person 2 appears in 2 photos, then the associations on person 5 should come before person 2.

The total number rows that will be returned will be the total number of row in the table, as I need ALL the associations (just ordered)

any help would be appreciated
Jason

[chazzy]

What if you did something like this.


select count(*) as cnt,individual_id from people_in_photo order by cnt;


Then maybe


select
whatever_columns
from
people p
inner join (select count(*) as cnt,individual_id from people_in_photo) pip_cnt
on pip_cnt.individual_id = p.individual_id
order by pip_cnt.cnt desc;


NOt tested, but should work on ansi standard systems

[abou.hmed]

I think it should be like this
i dnt have any tool to test it
so tell me if it will work
select count(Image_ID) as numberOfImage from people_in_photo group by Individual_ID order by numberOfImage