Sql Fun: Order before Group with a Sub Query

February 19, 2007 1-minute read

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