On Mon, Feb 19, 2001 at 11:06:21AM -0800, [EMAIL PROTECTED] wrote:
> I have a table with a date field, and would like to sort it
> on month and day. What I would like to do is:
>
>
> SELECT lastname,firstname,dob FROM table1 GROUP BY month(dob),day(dob)
>
> (where dob is a column of type 'date')
>
> but that doesn't work. I'm using MySQL 3.22.32. I saw one post about
> calculated fields, but that method didn't seem to work for me either.
> >From some other posts, it appears like I will have to resort to a temporary
> table. Is this correct? Am I missing something simple?
>
http://www.mysql.com/doc/G/r/Group_by_functions.html
(7.4.13 Functions for Use with GROUP BY Clauses)
! Note that if you are using MySQL Version 3.22 (or earlier)
! or if you are trying to follow ANSI SQL, you can't use
! expressions in GROUP BY or ORDER BY clauses. You can work
! around this limitation by using an alias for the expression:
!
! mysql> SELECT id,FLOOR(value/100) AS val FROM tbl_name
! GROUP BY id,val ORDER BY val;
In your case that would be something like:
SELECT lastname,firstname,dob,month(dob) as m,day(dob) as d
FROM table1 GROUP BY m,d;
Yes, unfortunately this gives you two more columns in your
output than you need. You'll have to ignore them in your
client.
Oh, you're mentioning that you want to sort (not group I
guess), so your query will be:
SELECT lastname,firstname,dob,month(dob) as m,day(dob) as d
FROM table1 ORDER BY m,d;
Finally, why don't you just ORDER BY dob. Don't you want the
year to be included in the sort? And if dob is a DATETIME
field, then don't you want the ordering on time as well?
If you don't specify additional ordering, there won't be any
guaranteed ordering for records with identical month and day
values anyway.
Regards,
Fred.
--
Fred van Engen XO Communications B.V.
email: [EMAIL PROTECTED] Televisieweg 2
tel: +31 36 5462400 1322 AC Almere
fax: +31 36 5462424 The Netherlands
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
