I don't know of any way to sort before the grouping. That would be peculiar, since the GROUP will be eliminating potentially many of those rows.
In your case, a simple solution should be: select jobnum, min(milestone), min(shipdate) from jobs group by jobnum order by 2, 3; I've always found it strange that mySQL lets us perform a GROUP BY while selecting arbitrary data from (some) row in the group. It seems vague (and I've never read the documentation on it) just what data it would choose. You are much better off picking the MIN, MAX, AVG, or something. Most DBMS's would require it. As a caution, the query I've shown above will potentially split information. If the row containint the min(milestone) for a certain jobnum is different from the row containing the min(shipdate), you'll get the information from those two rows. If you want to ensure you're seeing the shipdate from, for example, the minimum milestone for the job, then the query will be more complicated. Kevin > -----Original Message----- > From: Michael Winston [mailto:[EMAIL PROTECTED] > Sent: Wednesday, August 06, 2003 11:51 AM > To: [EMAIL PROTECTED] > Subject: newbie needs help: can I "order by" before "grouping"? > > > I'm having trouble ordering my results before grouping them. I don't > want to go into too much detail, so my simplified query without any > grouping or ordering results in: > > +--------+-----------+------------+ > | jobnum | milestone | shipdate | > +--------+-----------+------------+ > | 96-655 | 4 | | > | 96-655 | 2 | | > | 96-655 | 0 | 2003-12-04 | > | 98-308 | 0 | 1973-05-11 | > | 98-308 | 4 | | > | 98-316 | 0 | 1973-06-11 | > | 98-316 | 4 | | > | 98-500 | 2 | | > | 98-327 | 4 | | > | 98-327 | 0 | 1973-08-11 | > +--------+-----------+------------+ > > when I add "group by jobnum order by milestone" I get: > > +--------+-----------+------------+ > | jobnum | milestone | shipdate | > +--------+-----------+------------+ > | 98-308 | 0 | 1973-05-11 | > | 98-316 | 0 | 1973-06-11 | > | 96-655 | 4 | | > | 98-327 | 4 | | > +--------+-----------+------------+ > but what I really want is this: > > +--------+-----------+------------+ > | jobnum | milestone | shipdate | > +--------+-----------+------------+ > | 98-308 | 0 | 1973-05-11 | > | 98-316 | 0 | 1973-06-11 | > | 96-655 | 0 | 2003-12-04 | > | 98-327 | 0 | 1973-08-11 | > | 98-500 | 2 | | > +--------+-----------+------------+ > > Clearly, mysql is grouping first and then ordering. I suppose that's > reasonable, but I really want to order the results internally first > and then group so that the displayed row from each group is the > smallest value (and then maybe do some more sorting on shipdate, but > that's another issue). > > Is there a way to do this? > > Thanks, > Michael > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?> [EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
