Limiting results to one of each category

[RabidHumor]

I have a blog site where certain blogs may go into a category such as: Cellphone Clip of the Week or Prank of the Week.

I want a table at the top of the page to list which categories in order of which has the newest update.

mysql_query("SELECT * FROM posts WHERE category != '' ORDER BY date DESC")

I echo the category after this code and it almost does what i want.
How do I have it only display one of each category ordered by the date submitted?

thanks in advance!

[tomhath]

See if this gives you what you want. the ORDER BY 2 means order the results by the second field returned, in this case the max(date):

SELECT category, max(date) as max_date  FROM posts WHERE category != '' group by category ORDER BY 2 DESC

Or this might do it (I'm too lazy to test these suggestions):

SELECT category  FROM posts WHERE category != '' group by category ORDER BY max(date) DESC

[RabidHumor]

The second one you posted works! However now the problem is when I echo the results(e.g. $date) from that query, It posts the date from the earliest post and not the most recent, I'm stuck on how to get the right date to print.

[tomhath]

Try something like this:

SELECT p1.* FROM posts p1
WHERE p1.category != '' 
  and NOT EXISTS (
    select 1 from posts p2
    where p2.category = p1.category 
      and p2.date > p1.date)
ORDER BY p1.date DESC