SELECT last records for each ID with multiple JOINS in one query-CodePudding

I have these tables:

blocks TABLE        blocks_fields TABLE              
id|user_id|name     id|block_id|options_id|status    
 1|      1|block1    1|       1|         1|     1
 2|      1|block2    2|       2|         2|     0 
 3|      1|block3    3|       3|         3|     2
 4|      1|block4    4|       4|         1|     1

blocks_events TABLE        blocks_options TABLE
id|field_id|event|code     id|value1|value2
 1|       1| ev1 | 1234     1|    10|     A
 2|       2| ev1 | 5678     2|    20|     B
 3|       1| ev2 | 1234     3|    30|     C
 4|       3| ev1 | 9012     4|    10|     A

I'm trying to select the last event(last inserted row) for each code, knowing only the user_id

The output should be something like this:

blocks_events.event|blocks_events.code|blocks_options.value1|blocks_options.value2
                ev2|              1234|                   10|                    A
                ev1|              5678|                   20|                    B
                ev1|              9012|                   30|                    C

I know how to get to this output through multiple selects, but I would like to know if it's possbible to do it in just one select.

CodePudding user response：

This is just straightforward inner joins, using row_number to get only the latest event for each code:

select event,code,value1,value2
from (
    select be.event,be.code,bo.value1,bo.value2,row_number() over (partition by be.code order by be.id desc) rn
    from blocks b
    join blocks_fields bf on bf.block_id=b.id
    join blocks_events be on be.field_id=bf.id
    join blocks_options bo on bo.id=bf.options_id
    where b.user_id=1
) latest_events_for_code
where rn=1