[SOLVED] MySQL GROUP BY

[at0mic]

With reference to the tizag article "MySQL GROUP BY - Aggregate Functions" here http://www.tizag.com/mysqlTutorial/mysqlgroupby.php

 

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:

 

$today = getdate();

$query = "SELECT type, date FROM products WHERE date > '$today' GROUP BY type"; 

 

I could do a seperate query for each type but it would be nice if there's a way to do it with one query.

[cbolson]

Hi,

You could try something like this:

 

$query = "SELECT DISTINCT(type), MAX(date) FROM products WHERE date > curdate() ORDER BY date DESC"; 

Not 100% sure about it but worth a try

 

Chris

[at0mic]

Thanks for your post but your code gave the error:

 

Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

 

I added the GROUP BY clause but some of the dates were in the past. Any other ideas?

 

Here's the actual code I used

 

$query = "SELECT DISTINCT(pf_type), MAX(pf_date), pf_date FROM paignton_fixtures WHERE pf_date > curdate() GROUP BY pf_type ORDER BY pf_date DESC"; 

[PFMaBiSmAd]

If you would post some of your data and the results you are getting it would help. Specifically show what you are getting for pf_date and what the expected value should be for any group of pf_type.

 

Also, DISTINCT is not a function that applies to column names. It removes duplicate ROWS in the result set.

[at0mic]

Here is the code I'm using:

 

$query = "SELECT pf_type, MIN(pf_date) FROM paignton_fixtures WHERE pf_date > curdate() GROUP BY pf_type"; 
$result = mysql_query($query) or die(mysql_error());

while( $row = mysql_fetch_array( $result ) )
{
$pf_date = date('d M y', $row['MIN(pf_date)']);

echo "<tr><td>{$row['pf_type']}</td><td>$pf_date</td></tr>";
}

 

This retrieves the following from MySQL

 

TypeDate

Colts22 Aug 09

Development22 Aug 09

First08 Aug 09

Ladies03 Oct 09

Vets12 Sep 09

 

The pf_type part is working ok because it correctly retrieves one of each.

 

However the date part isn't working because 08 Aug 09 is in the past. I want it to retrieve the next dates in the future for each type. Not an apparently random date.

[PFMaBiSmAd]

What does pf_date look like in your database table?

[at0mic]

pf_date is the number of seconds that have passed since Jan 1 1970

[PFMaBiSmAd]

Comparing a Unix Timestamp with curdate() (or the getdate() value that was in your first post) is meaningless because they are not in the same units/format.

 

Do the values include an actual time in any day or are they at midnight of a day with zero seconds for the time? Also, what do you want to compare them with? The current Unix Timestamp (with whatever the current number of seconds in the day is) or do you want to compare them with a Unix Timestamp that represents the start of the current day?

 

You would be better off using a mysql DATE yyyy-mm-dd or DATETIME yyyy-mm-dd hh:mm:ss data type (depending of if you need the time.)

 

 

[at0mic]

Of course yes you're right. I just changed it to time() and it worked perfectly.

 

So thanks cbolson for your query and thanks PFMaBiSmAd for the date info.