> The distinct can only work as you want it to work when you only select the
> date column and only the date part (not the time part).
> Unfortunately I'm more an oracle DBA where the date functions are more clear
> to me so can not help with exact syntax.
>
> I think what you should be doing is returning the entire set of records (all
> required columns) in sorted by date order and loop through all of them. keep
> track of the last date processed and if it is the same as the current
> record, process nothing and go get the next record.
>
>
> BUT.... I am not quite sure what you are trying to achieve so my advise may
> be completely flawed.
>
> Jack
> 2009/8/3 Govinda <govinda.webdnatalk@xxxxxxxxx>
Doh, should have realised this before, but it's after 4 AM here.
Jack is absolutely correct here, you are getting all the rows back
because that is what the query asks for. Try this as your first step
SELECT DISTINCT DATE(solarLandingDateTime) AS solarLandingDate
FROM " . $whichTable . " ORDER BY solarLandingDateTime DESC
LIMIT $Maxrecs2Show") or die("query failed: " . mysql_error());
This should get your unique rows by date. The previous queries used ALL
of the column names to form a unique row, and that made all the rows
DISTINCT.
Give us an idea of your table structure, if possible post the CREATE
statement for it.
> > 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
> >
> >
>
>
> --
> Jack van Zanen
>
> -------------------------
> This e-mail and any attachments may contain confidential material for the
> sole use of the intended recipient. If you are not the intended recipient,
> please be aware that any disclosure, copying, distribution or use of this
> e-mail or any attachment is prohibited. If you have received this e-mail in
> error, please contact the sender and delete all copies.
> Thank you for your cooperation
--
Niel Archer
niel.archer (at) blueyonder.co.uk
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
