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.