MySQL GROUP BY

silverf0x

With reference to tizag article MySQL GROUP BY - Aggregate Functions

I've got something similar with an additional date collumn. I want to show one of each type where the date is most recent (but not past).

I've tried the following but it doesn't work:

$query = "SELECT type, date FROM products WHERE date > getdate() GROUP BY type";

Roger_Ramjet

SELECT type, MIN(date) FROM products WHERE date > getdate() GROUP BY type, date

silverf0x

Thanks for the reply. Here's the exact query I'm using:

SELECT pf_type, MIN(pf_date) FROM fixtures WHERE pf_date > curdate() GROUP BY pf_type, pf_date

It retreived multiple types. So I removed the date from the end of the query and now the Type is working ok but not the date (22 Aug 09 is in the past).

Type		Date 

Colts		22 Aug 09 
Development	22 Aug 09 
First		08 Aug 09 
Ladies		03 Oct 09 
Vets		12 Sep 09

Anymore ideas?

bretticus

I believe you can dump pf_date in the GROUP BY clause since you ask for MIN(pf_date). Never hurts to try 🙂

Roger_Ramjet

bretticus;10927200 wrote:
I believe you can dump pf_date in the GROUP BY clause since you ask for MIN(pf_date). Never hurts to try 🙂

Yes you can in MySQL - but not in most other RDBMS.