CODEWITHSUNDEEP
sqlmysqlgreatest-n-per-group
Joshua Fricke
Joshua Fricke

Reputation: 391

Mysql select grouping

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".

Upvotes: 0

Views: 132

Answers (2)

OhioDude
OhioDude

Reputation: 1080

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.

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562871

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;

Upvotes: 1

Related Questions