Sql Fun: Order before Group with a Sub Query

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 sub query 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 sub query 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