Newest items, query logic

[jR³]

Hi
I want a query that does the following thing,
Select the 10 latest items added in my database, but only the latest item of each category.
databaselayout:
Category
id, name
Item
id, name, dateadded, categoryid

Query should be something like :
select * from Item
group by categoryid
having count(categoryid)=1
order by dateadded desc
limit 0,10

but I get 0 rows returned.. What's wrong with my query logic?
thanks in advance!

[lilleman]

Hi,

Try removing the HAVING clause. Any change?

Yours, Erik.

[r937]

select * 
  from Item as o
 where dateadded
     = ( select max(dateadded)
           from Item
          where categoryid = o.categoryid )
limit 10

[jR³]

select * 
  from Item as o
 where dateadded
     = ( select max(dateadded)
           from Item
          where categoryid = o.categoryid )
limit 10

Ok thanks, it worked out well,
that good 'old max() always forgetting that one
final query looks like this.. quite slow one :s
(working with more than 1 category for an item )

"select ic.categoryid,i.itemid,i.name,i.urlname,location,dateadded 
from items i
	inner join itemcategory ic on i.itemid=ic.itemid
	inner join categories c on ic.categoryid=c.id		
	where categorytype=$categoryType
	and dateadded =( select max(dateadded)
           			from items i2
           			inner join itemcategory ic2 on i2.itemid=ic2.itemid
           		        where ic2.categoryid=ic.categoryid )
order by dateadded desc
limit 0,$offset"

Well I wonder if this will work for mysql < 4.1..
only tested it out on 4.1.. but my production server is 4.0
let's hope there's no need to rewrite it as a join

[lilleman]

Hi,

The query will not work on versions lower than 4.1, since it's using a subselect.

Yours, Erik.

[r937]

i don't think you need that join in the subquery

you want the latest item in each category

the item table has a column called categoryid

that's sufficient