Re: newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?

On Aug 3, 2009, at 12:29 AM, Govinda <govinda.webdnatalk@gmail.com> wrote:

Oops, forgot to mention that with the alias you can change the ORDER BY clause to use the aliased column data: ORDER BY solarLandingDate DESC this will only use the returned data instead of the entire column.

If you are aliasing a column it is better to use the optional AS keyword to avoid confusion. MySQL's DATE function returns dates formatted as 'YYYY-MM-DD' so DATE_FORMAT is not needed here.

Niel, Bastien, thanks for your efforts to lead me to understanding this! I tried everything you both suggested. Ideally I would have some clear docs that outline the syntax for me, for such an example as I need.. and I would be able to check my code myself. Meanwhile, In every case, I just get every record in the table back as a result. So then I thought, "try and make even a *simple* DISTINCT work, and then move on to the date thing"... so I try this: //$foundTrackingRows=mysql_query("SELECT DISTINCT solarLandingDir, solarLandingIP, solarLandingDir, solarLandingDateTime FROM ".$whichTable." ORDER BY solarLandingDateTime DESC LIMIT $Maxrecs2Show") or die("query failed: " .mysql_error()); In all the records in this table, there are only 3 possible values in the 'solarLandingDir' column (TINYTEXT): diysolar solar_hm (null) but I still get all the records back, with each distinct 'solarLandingDir' column value represented several times. So something really basic is missing in my understanding/code. Can you see what it is? -Govinda --PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php

The issue is with the select distinct, if you wrap the date in the parantheses for the with the distinct, the example I sent last night works fine. Select distinct ( date_format( solarLandingDate , '%Y-%m-%d')), solarLandingIP,... If you don't place the distinct parentheses around the date, the engines tries for a distinct on the entire row, which is why you end up with all rows Bastien Sent from my iPod