Get the Most Current Row From a Detail Table

  • Comments posted to this topic are about the item Get the Most Current Row From a Detail Table

    God is real, unless declared integer.

  • Hi,

    Or you can just make this measure always available with latest value and no expencive actions by just updating customer table every time a customer doing a purchase.

    Simple and value always latest 🙂


  • this may work for a customer + the last order but there are tons of other scenarios where the updates comes much more often or it is not worth the afford to track the last xxx, particularly since it would need a trigger (inserts to the child table can come from everywhere, not just your application).

    And when you have to ensure 100% to have the very last entry (imagin a price table where it would have financial impact). The price table is a good example for another reason too - prices comes usually with a valid_from and you don't want to use today the price that will become valid tomorrow. And you don't want to update your product table every minute (prices could change multiple time per day) to set always the most current price.

    God is real, unless declared integer.

  • I would typically use a JOIN with MAX query for this kind of thing.

    Something like the SQL below, did you already rule this out for some reason?

    update #tbl
    set last_column = last_column_query.column_name
    #tbl.object_id ,
    from #tbl
    join #columns on #columns.object_id = #tbl.object_id and #columns.column_id = (select MAX(c2.column_id) from #columns c2 where c2.object_id = #tbl.object_id)
    ) as last_column_query
    where #tbl.object_id = last_column_query.object_id
  • I agree, that it could be a valid solution too (depending on your real workload).

    But you should change your query a bit to make it faster (place alias of the subquery instead of #tbl behind the UPDATE and add the column, that you want to update into the column list of the subquery):

    UPDATE last_column_query
    SET last_column_query.last_column = last_column_query.column_name
    (SELECT #tbl.object_id
    , column_name
    , last_column
    FROM #tbl
    JOIN #columns
    ON #columns.object_id = #tbl.object_id
    AND column_id = (SELECT MAX(c2.column_id)FROM #columns AS c2 WHERE c2.object_id = #tbl.object_id)
    ) AS last_column_query

    This way you save a self join of the #tbl.

    God is real, unless declared integer.

  • How about avoiding UPDATE altogether, this query seemed fast enough to me:


    WITH Last_Columns AS
    SELECT object_id, MAX(column_id) AS LastCol
    FROM #columns
    GROUP BY object_id
    SELECT T.Object_ID, C.LastCol
    FROM #tbl AS T
    JOIN Last_Columns AS C ON C.object_id = T.Object_id

    Zidar's Theorem: The best code is no code at all...

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply