Select n most recent of each category

multiple_groups.sql

CREATE TABLE genus (
  id int auto_increment primary key, 
  name varchar(20)
);

INSERT INTO genus (name) VALUES 
  ('Brachypelma'),
  ('Avicularia'),
  ('Nhandu'),
  ('Euathlus'),
  ('Grammostola');

CREATE TABLE species (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(20),
  category_id INT NOT NULL,
  created_at DATETIME
);

INSERT INTO species (name, category_id, created_at) VALUES 
  ('smithi', 1, '2013-09-01'),
  ('avic', 2, '2013-09-02'),
  ('chomatus', 3, '2013-09-03'),
  ('red', 4, '2013-09-04'),
  ('rosea', 5, '2013-09-05'),
  ('albopilosum', 1, '2013-09-05'),
  ('urticans', 2, '2013-09-04'),
  ('coloratovilosus', 3, '2013-09-03'),
  ('orange', 4, '2013-09-02'),
  ('pulchra', 5, '2013-09-01'),
  ('anitas', 1, '2013-09-01'),
  ('metallica', 2, '2013-09-03'),
  ('anole', 3, '2013-09-05'),
  ('blue', 4, '2013-09-02'),
  ('pulchripes', 5, '2013-09-04');

SELECT * FROM ( SELECT
  c.name AS category, 
  c.slug AS category_slug,
  a.title AS title, 
  a.slug AS article_slug, 
  a.creation_date AS creation_date
FROM news_articles a 
INNER JOIN news_categories c ON a.category_id = c.id 
WHERE c.id IN (1,2,3)
ORDER BY creation_date DESC, category ) AS articles
GROUP BY category;

(SELECT g.name AS genus_name, s.name AS species_name, s.created_at AS created_at FROM species s, genus g WHERE s.genus_id = 1 LIMIT 2)
UNION
(SELECT g.name AS genus_name, s.name AS species_name, s.created_at AS created_at FROM species s, genus g WHERE s.genus_id = 2 LIMIT 2)
UNION
(SELECT g.name AS genus_name, s.name AS species_name, s.created_at AS created_at FROM species s, genus g WHERE s.genus_id = 3 LIMIT 2)
ORDER BY genus_name, created_at;

--http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

-- want to show most recently created species grouped in multiples of 2 by genus, LIKE:
-- | Brachypelma | albopilosum |
-- | Brachypelma | smithi      |
-- | Nhandu      | anole       |
-- | Nhandu      | chromatus   |
-- | Avicularia  | urticans    |
-- | Avicularia  | metallica   |