[SOLVED] ORDER BY / GROUP BY issue

[chunwc]

Hi there,

I'm stuck on a bit of mySQL which I can't seem to resolve at all. I've tried using two methods, one of which is GROUP BY. This is new for me, so I may be using it wrong.

 

Here's the table I am interested in querying:

 

(I have removed fields that aren't in the query)

 

CREATE TABLE `headl` (

  `headlid` mediumint( unsigned NOT NULL auto_increment, //that's an eight in brackets, not an emoticon!

  `headldate` date NOT NULL,

  `comps_id` mediumint( unsigned NOT NULL, //that's an eight in brackets

  `headlnoshow` enum('Y','N') NOT NULL default 'N',

  PRIMARY KEY  (`headlid`)

) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=22 ;

 

Here is my data so far (I have removed the first 9 lines as they are headlnoshow='Y')

 

headlid headldate comps_id headlnoshow

10 2009-09-14 90 N

11 2009-09-15 91 N

12 2009-09-15 91 N

13 2009-09-15 91 N

14 2009-09-15 91 N

15 2009-09-01 24 N

16 2009-08-27 24 N

17 2008-10-29 24 N

18 2009-10-21 26 N

19 2009-07-02 108 N

20 2009-09-17 108 N

21 2009-11-09 21 N

 

What the data represents is a 'headline' attributed to a particular brand ('comps_id') and the date of that headline. The last column represents whether the data should be available to be viewed or not (N means it can be viewed).

 

What I am trying to is identify 5 brands that have headlines most recently (and where headlnoshow=N). In other words, I would expect to see lines (headlids) 21, 18, 20, etc. What I only need though is the brand (comps_id)

 

My original query was this:

SELECT DISTINCT comps_id FROM headl WHERE headlnoshow='N' ORDER BY headldate DESC LIMIT 5

 

which gets me ids of 21, 26, 91, 90 and 24

 

I eventually discovered GROUP BY and came up with this:

SELECT comps_id FROM headl WHERE headlnoshow='N' GROUP BY comps_id ORDER BY headldate DESC LIMIT 5

 

however, this gave me exactly the same results.

 

The actual query result I am hoping to see is 21, 26, 108, 91, 90.

 

Any ideas how I can achieve this? For some reason it is putting 108 as the 6th item here (based on the date of headlid 19 i presume).

 

Many thanks in advance, and apologies for any obvious oversight but I'm stuck on this one

 

[PFMaBiSmAd]

$query ="SELECT comps_id FROM (SELECT comps_id, MAX(headldate) as hldate FROM headl WHERE headlnoshow='N' GROUP BY comps_id) a ORDER BY hldate DESC LIMIT 5";

[chunwc]

$query ="SELECT comps_id FROM (SELECT comps_id, MAX(headldate) as hldate FROM headl WHERE headlnoshow='N' GROUP BY comps_id) a ORDER BY hldate DESC LIMIT 5";

 

Many thanks, this works perfectly. I had tried subqueries but hadn't got the syntax for that right either.