complex Mysql query/sorting help

I have this table structure:

name | timeUpdate | approved | private | Product

where timeUpdate is a mysql timestamp

and the table can have many names with many different products but some of the names and products are the same too.

like:

joe | timestamp | 1 | 0 | widget respectively.

what I need to do is total up how many different names have chosen the same product and and sort them by the most recent timestamp and have the product that is chosen the most at the top.

So far I have this code:

select *, COUNT(name) from archive 
WHERE DATE_SUB(CURDATE(),INTERVAL 1 DAY) <= timeUpdate 
and (approved='1' and private='0' )  GROUP by product  order by
 COUNT(name) DESC, id  Limit 20 ;

Code (markup):

Which works but does not sort them by the most recent entry. It does sort by the product with most counts though.

Any ideas On how to fix it so that the most recent updated items are listed first but also the ones with the most count are at the top?


Thanks in advance!

 

Just add 'timeUpdate DESC' to the 'ORDER BY' clause.

 

phper

Thanks....I was banging my head over this.....I didn't know you could have more than one DESC separated by commas so i did what you said and....I also had to add DESC to 'id' too.........now it works perfectly!

Thanks!!

 

Any idea why this part of the code doesnt work?

and (approved='1' and private='0' )

Code (markup):

if private is = 1 then the whole query still runs and shows all rows that have private whether it is set to '0' or '1'

It seems as if everything in the and clause is ignored.

So how do I filter it to show only private that = 0 ?

 

Ok I guess Im just having an off day......it does appear to be working so ignore my last post please

 

Thanks for the tutorial