select n posts per category in a single query

In my home page I show different posts from different categories. My current implementation is to call query_posts many times (once per category)

How can I use one query to pull out the data?

My tries

1 - this method works(ignore the ugly very long sql,I have many categories...) Thanks the post: http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

( SELECT * 
  FROM `wp_posts` p,`wp_term_relationships` rel 
  WHERE rel.object_id = p.ID 
   AND rel.term_taxonomy_id = '3' 
  ORDER BY p.post_date DESC 
  LIMIT 2)
UNION ALL 
( SELECT * 
  FROM `wp_posts` p,`wp_term_relationships` rel 
  WHERE rel.object_id = p.ID 
    AND rel.term_taxonomy_id = '4' 
  ORDER BY p.post_date DESC 
  LIMIT 2)

Answers


SELECT i.* 
FROM wp_term_relationships rel
INNER JOIN ( 
  SELECT p.*, rel2.* 
  FROM wp_posts p
  INNER JOIN wp_term_relationships rel2
          ON (rel2.object_id = p.ID) 
  WHERE rel2.term_taxonomy_id = rel.term_taxonomy_id
  ORDER BY p.post_date DESC 
  LIMIT 2 OFFSET 0 ) i ON (i.object_id = rel.object_id)
WHERE rel.term_taxonomy_id IN ('3','4')
ORDER BY i.term_taxonomy_id ASC, i.post_date DESC

Need Your Help

Scanning a polygon like geographical area

math geometry gis geography area

I have a geographical area that has a polygon like shape. I want to scan that area with determined steps, say ~25-30 meters per step. I'm using lat/long system. All I need is a way to do this typ...

redirect through a form without giving an error

php javascript ajax

I'm trying to send visitors to http://www.facebook.com/plugins/comments.php?href=http://google.com?c

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.