I have a table containing two fields, 'user' and 'product'. Every 'user' can have several 'product' entries, and every 'product' can have several 'user' entries. This is basically the linking table in an m<->m relationship between the 'user' and the 'product' tables.

Now, I have a list 'users' and I want to retrieve the following information: an order list of 'products' which are the most popular among those 'users'. I only need the number of users for a given product, and I need the product ordered by the number of users.

So, if my user list is (1,2,3,4) and my table is something like
then I would like to get

I know who I can do that by a simple select statement ordering on the 'products', and some calculations in perl, but I wonder if there is direct way in SQL?

Thanks for any help.

Ivan
-----
Iyengar Yoga Resources / GT Plugins

Hi,

Something like:

select product, count(*) as hits
from table
where user in (1,2,3,4)
group by product
order by hits desc

should work.

Cheers,

Alex
--
Gossamer Threads Inc.

Thanks for the reply.

So, in terms of GT::SQL methods, this would be:

Ivan
-----
Iyengar Yoga Resources / GT Plugins

Thanks a lot again. This solution gave a speed increase of 400% (i.e. four times faster) than my previous brute-force perl solution.

Ivan
-----
Iyengar Yoga Resources / GT Plugins