SQL: MySQL gets the first row using group by

Posted on May 9, 2009

0


It came to a situation when I was required to create an SQL SELECT statement of table A. At the same time, the table primary key became a foreign key in table B. Table B had multiple entries for each foreign key from table A. Adding in to the existing SQL SELECT statement, I was required to retrieve the first row out of table B for each foreign key from table A.

Table A

id name

Table B

id a_id (F_KEY) name

I came out with this:

SELECT
a.*, b.b_id, b.b_name
FROM TableA AS a
LEFT JOIN (
SELECT id AS b_id, a_id, name AS b_name FROM TableB GROUP BY a_id
) AS b ON a.id=b.a_id;

Amazingly, the solution worked, well, at least to retrieve an accurate information. I am still looking for a better query to work with.

Advertisements
Tagged:
Posted in: MyWay, Technology