Issues returning max date for price from multiple tables in SQL

Question

First time poster and rather new to SQL so be kind :D

I have an issue with an SQL query that I'm trying to make to pull a stock list together.

I need to pull the SalePrice with the last modified date from the StockItem_Pricing Table.

I've included the query I have so far below.

SELECT 
    sc.ChannelSKU AS 'sku',
    cb.Level_LessOrderBook AS 'quantity',
    cb.InOrderBook AS 'In Open Orders',
    cb.MinimumLevel AS 'Minimum Level',
    cb.Level AS 'Stock Level',
    si.BarcodeNumber AS 'Barcode Number',
    sp.SalePrice AS 'product_price_vat_inc',
    sp.SalePrice AS 'Retail Price',
    cb.ItemTitle AS 'Title',
    sc.Source,
    sc.SubSource,
    sp.ModifiedDate AS 'LastUpdate'
FROM 
    Stock_ChannelSKU sc
INNER JOIN 
    View_CombinedStock cb ON cb.pkStockItemId = sc.fkStockItemId
INNER JOIN 
    StockItem si ON si.pkStockItemID = sc.fkStockItemId
INNER JOIN 
    StockItem_Pricing sp ON sp.fkStockItemId = sc.fkStockItemId
WHERE  
    sc.SubSource = 'UM_8292775'

Thanks in advance :D

Answer 1

If you just want one row, then use TOP() and ORDER BY:

select top (1)
    sc.ChannelSKU AS sku,
    cb.Level_LessOrderBook AS quantity,
    cb.InOrderBook AS InOpenOrders,
    cb.MinimumLevel AS MinimumLevel,
    cb.Level AS StockLevel,
    si.BarcodeNumber AS BarcodeNumber,
    sp.SalePrice AS product_price_vat_inc,
    sp.SalePrice AS RetailPrice,
    cb.ItemTitle AS Title,
    sc.Source,
    sc.SubSource,
    sp.ModifiedDate as LastUpdate
from Stock_ChannelSKU sc
inner join View_CombinedStock cb on cb.pkStockItemId = sc.fkStockItemId
inner join StockItem si on si.pkStockItemID = sc.fkStockItemId
inner join StockItem_Pricing sp on sp.fkStockItemId = sc.fkStockItemId
where sc.SubSource = 'UM_8292775'
order by sp.ModifiedDate desc

If you want the latest row per sku, then an alternative is row_number():

select *
from (
    select
        sc.ChannelSKU AS sku,
        cb.Level_LessOrderBook AS quantity,
        cb.InOrderBook AS InOpenOrders,
        cb.MinimumLevel AS MinimumLevel,
        cb.Level AS StockLevel,
        si.BarcodeNumber AS BarcodeNumber,
        sp.SalePrice AS product_price_vat_inc,
        sp.SalePrice AS RetailPrice,
        cb.ItemTitle AS Title,
        sc.Source,
        sc.SubSource,
        sp.ModifiedDate as LastUpdate,
        row_number() over(partition by sc.ChannelSKU order by sp.ModifiedDate desc) rn
    from Stock_ChannelSKU sc
    inner join View_CombinedStock cb on cb.pkStockItemId = sc.fkStockItemId
    inner join StockItem si on si.pkStockItemID = sc.fkStockItemId
    inner join StockItem_Pricing sp on sp.fkStockItemId = sc.fkStockItemId
    where sc.SubSource = 'UM_8292775'
) t
where rn = 1