MySQL Need advice on Query

Question

I want to fetch latest 3 news from each news type.

CREATE TABLE IF NOT EXISTS `news` (
  `news_id` int(8) NOT NULL AUTO_INCREMENT,
  `news_heading` tinytext NOT NULL,
  `news_description` text NOT NULL,
  `news_date` date DEFAULT NOT NULL,
  `news_type` tinyint(1) NOT NULL COMMENT '0- PEP|1 - MEDIA|2 - CONSULTING',
  `created_date` datetime NOT NULL,
  `modified_date` datetime NULL,
  `display` tinyint(1) NOT NULL COMMENT '0- ON | 1 -OFF',
  PRIMARY KEY (`news_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

Below Query will give me only 1 latest news from all type. Suggest me how we can achieve for top 3 from each type

SELECT * FROM (
SELECT * FROM  `news` 
ORDER BY  `created_date` DESC
) AS TBL
GROUP BY  `news_type`

Answer 1

Try this:

SELECT news_id, news_heading, news_description, news_date, 
       news_type, created_date, modified_date, display
FROM (SELECT news_id, news_heading, news_description, news_date, 
             news_type, created_date, modified_date, display, 
             IF(@news_type = @news_type:=news_type, @id:=@id+1, @id:=1) AS id 
      FROM news, (SELECT @id:=1, @news_type:=0) A 
      ORDER BY news_type, created_date DESC
     ) AS A 
WHERE id <= 3;