Mysql select grouping

Question

I have two tables I need to select data from TABLE_A and TABLE_B; they have a one to many relationship.

In my select statement I will often get multiple unique results from TABLE_A and this is fine. But I will also get multiple matches in TABLE_B - I need to get the most recent TABLE_B record that matches. I have an auto incremented id tag that is available.

Here is a more detailed example:

TABLE_A

TABLE_A_id, data

1, something

2, somethignelse

3, yetagainsomething

TABLE_B

TABLE_B_id, TABLE_A_id, data

1, 1, filler_data1

2, 1, filler_data1

3, 1, filler_data3

4, 2, filler_data4

5, 2, filler_data5

6, 3, filler_data1

I need to select the data such that my returned array is something like this for a search on rows containing "filler_data1":

TABLE_A_id = 1, something, TABLE_B_id = 2, filler_data1

TABLE_A_id = 3, yetagainsomething, TABLE_B_id = 6, filler_data1

So in the above case I get the TABLE_B data which is the most recent, i.e. TABLE_B_id = 2 and matches the search of "filler_data1".

Answer 1

This is the "greatest N per group query" question that comes up several times per week on StackOverflow.

SELECT A.*, B1.*
FROM TABLE_A A
JOIN TABLE_B B1 ON (A.A_ID = B1.A_ID)
LEFT OUTER JOIN TABLE_B B2 ON (A.A_ID = B2.A_ID AND B1.B_ID < B2.B_ID)
WHERE B2.B_ID IS NULL;

Answer 2

One approach I have taken in the past is to create a view of table_b that contains the most current information, then join table_a to that. Case in point. We have an incident tracking system. One table holds all the constant data related to the incident, table_a. The second table, table_b, acts as an audit table and tracks updates to the incident. I created a view of the most recent updates for each specific incident, I use this view for various reports, but I can join this table to my table_a to yield results that produce the latest information on the incident.