Hi Jeromie,
Thanks for the info. I've fixed the problem by creating a temp table
and incorporating that into the the final query. I can't do
subqueries, as the PHP on the server is pre 4.1. Thanks again for you
suggestions.
Mark
On Feb 8, 2006, at 10:29 PM, Jeromie Clark wrote:
Hi -
I don't have a way to test it, so I'm not sure, but I'll try and point
you in the right direction.
I'm guessing that either the GROUP BY or the Function you're running on
the date in the SELECT is hosing you.
You should be able to test quickly by just omiting those things.
If the dates order magically afer removing option 1, consider doing the
same thing, but with a subquery.
If number 2 fixes you, consider fetching an unaltered copy of the date
with SELECT AS and sorting with that.
If neither helps, shoot me a dump of your example with schema and data
off-list, and I'll figure it out when I'm at work tomorrow.
Jeromie
-----
> SELECT i.itin_id, d.destination, i.nights,
> DATE_FORMAT(MIN(sailing_date), '%b %e, %Y') AS minsd
>
> FROM itineraries AS i, destinations AS d, ships AS s, cruise_lines AS
> cl, sailings AS sa
>
> WHERE i.destination = d.dest_id AND i.cline = cl.cline_id AND
i.ship =
> s.ship_id AND i.itin_id = sa.itin_id
>
> GROUP BY i.itin_id
>
> ORDER BY destination, sailing_date
> -------------------------------------------------------------
>
> The result I am trying to get would be this:
>
> -------------------------------------------------------------
> itin_id destination nights minsd
> 2765 Bahamas 12 2006-02-20
> 8451 Europe 8 2006-01-31
> -------------------------------------------------------------
>
> Does any of this look close or am I way off?
>
> If any one has any ideas, let me know. All I can think to do right
now
> is to possibly make a temporary table before this query is made and
> incorporate that table into this query.
>
> Thanks for you help,
>
> Mark
>
> ----------------------------------------
>
> On Jan 16, 2006, at 4:15 PM, Mark Wheeler wrote:
>
> Hi,
>
> I don't know if I am giving you enough information to diagnose the
> problem, but here goes. I am trying to sort some dates, but they
> seem
> to not be sorting correctly so, for example, February 23, 2006
comes
> before February 11, 2006. Here is the query:
>
> -----------------------------------------------------------
> SELECT i.itin_id, d.destination, i.nights,
> DATE_FORMAT(MIN(sailing_date), '%b %e, %Y') AS minsd
>
> FROM itineraries AS i, destinations AS d, ships AS s,
cruise_lines AS
> cl, sailings AS sa
>
> WHERE i.destination = d.dest_id AND i.cline = cl.cline_id AND
i.ship
> =
> s.ship_id AND i.itin_id = sa.itin_id
>
> GROUP BY i.itin_id
>
> ORDER BY destination, sailing_date
> ------------------------------------------------------------
>
> The "MIN()" call is supposed to go through the dates associated
with
> the "i.itin_id" (they are stored in a different table) and find the
> earliest date. I'm at a bit of a loss.
>
> Does any one have any idea?
>
> Thanks,
>
> Mark
>
>
>
> The php_mysql group is dedicated to learn more about the PHP/MySQL
web
> database possibilities through group learning.
>
>
> YAHOO! GROUPS LINKS
>
> ▪ Visit your group "php_mysql" on the web.
>
> ▪ To unsubscribe from this group, send an email to:
> [EMAIL PROTECTED]
>
> ▪ Your use of Yahoo! Groups is subject to the Yahoo!
Terms of
> Service.
>
>
>
>
> The php_mysql group is dedicated to learn more about the PHP/MySQL
web
> database possibilities through group learning.
> Yahoo! Groups Links
>
>
>
The php_mysql group is dedicated to learn more about the PHP/MySQL
web database possibilities through group learning.
SPONSORED LINKS
American general life and accident insurance company American general
life insurance company American general life
American general mortgage American general life insurance Computer
internet security
YAHOO! GROUPS LINKS
Visit your group "php_mysql" on the web.
To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
The php_mysql group is dedicated to learn more about the PHP/MySQL web database
possibilities through group learning.
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/php_mysql/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
