Get the Most Current Row From a Detail Table

Question

Get the Most Current Row From a Detail Table

Thomas Franz

Hall of Fame

Points: 3874
More actions
July 10, 2023 at 12:00 am

#4214683

Comments posted to this topic are about the item Get the Most Current Row From a Detail Table
God is real, unless declared integer.

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

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

etheral1980 Old Hand Points: 303 More actions · Answer 1

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 🙂

Thomas Franz Hall of Fame Points: 3874 More actions · Answer 2

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.

tripleAxe SSCertifiable Points: 5706 More actions · Answer 3

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
from 
(
select 
#tbl.object_id , 
#columns.column_name
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

Thomas Franz Hall of Fame Points: 3874 More actions · Answer 4

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
  FROM
      (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.

Zidar SSC Journeyman Points: 86 More actions · Answer 5

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