SQL fun: order before group with a subquery

19.02.2007 at 17:49

I have came across a problem within a sql statement. Say we have 2 tables which are in a 1:n relationship. So we want to join them together normally the sql engine will just use the first match based on the auto increment key within the table. This isn't what i needed so i searched a way to actually sort the data before the grouping happened. The solution seems to be a subquery which isn't ideal performance wise but worked for my use case. So i ended up with something like this:

SELECT * FROM (
    SELECT lastname, firstname, address_addition FROM address
    LEFT JOIN othertable ON address.somefield = othertable.somefield
    ORDER BY whatever
) as addresses GROUP BY addresses.address_id

Which brought me to the next problem, the sql standard doesn't allow duplicate column names within a subquery and mysql does actually implement this part of the standard -- surprise ;)

So i had to "retag" a few fields in the selected clause with AS. Lesson learned: always use a short table prefix.

Marc

Comments (1)

i was searching for this!

01.08.2008 by haslina (haslina.hashim[at]gmail[dot]com)

hiya, thanks for putting this up. I was searching for this solution! i also need to order my results before grouping them.

1