SQL Query to Get Latest Price

SQL Query to get latest price

I think the only solution with your table structure is to work with a subquery:

SELECT *
   FROM Thing
   WHERE ID IN (SELECT max(ID) FROM Thing 
                   WHERE ThingID IN (1,2,3,4)
                   GROUP BY ThingID)

(Given the highest ID also means the newest price)

However I suggest you add a "IsCurrent" column that is 0 if it's not the latest price or 1 if it is the latest. This will add the possible risk of inconsistent data, but it will speed up the whole process a lot when the table gets bigger (if it is in an index). Then all you need to do is to...

SELECT *
   FROM Thing
   WHERE ThingID IN (1,2,3,4)
     AND IsCurrent = 1

UPDATE

Okay, Markus updated the question to show that ID is a uniqueid, not an int. That makes writing the query even more complex.

SELECT T.* 
   FROM Thing T
   JOIN (SELECT ThingID, max(PriceDateTime)
            WHERE ThingID IN (1,2,3,4)
            GROUP BY ThingID) X ON X.ThingID = T.ThingID 
                                AND X.PriceDateTime = T.PriceDateTime
   WHERE ThingID IN (1,2,3,4)

I'd really suggest using either a "IsCurrent" column or go with the other suggestion found in the answers and use "current price" table and a separate "price history" table (which would ultimately be the fastest, because it keeps the price table itself small).

(I know that the ThingID at the bottom is redundant. Just try if it is faster with or without that "WHERE". Not sure which version will be faster after the optimizer did its work.)

Get most recent Price for each Item

The idea behind the subquery is it separately gets the latest PurchaseDate for each ItemID. The result of the subquery is then joined back on the table provided that it matches on two conditions: ItemID and PurchaseDate.

SELECT  a.*
FROM    TableName a
        INNER JOIN
        (
            SELECT  ItemID, MAX(PurchaseDate) max_date
            FROM     TableName
            GROUP   BY ItemID
        ) b ON  a.ItemID = b.ItemID AND
                a.PurchaseDate = b.max_date

SQL query to get the latest price from the data?

We can try using ROW_NUMBER for this requirement:

WITH cte AS (
    SELECT t.*, ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY time DESC) rn
    FROM yourTable t
    WHERE system_date = date '2019-12-19'
)

SELECT local_date, system_date, currency, product_id, time, current_price
FROM cte
WHERE rn = 1;

SQL - How to get latest price based on effective date?

One method is a correlated subquery:

select t.*
from t
where t.effective_date = (select max(t2.effective_date)
                          from t t2
                          where t2.id = t.id and
                                t2.effective_date <= '2020-07-06'
                         );

Query to find the prices of all products on 2019-08-16

Your solution below (I let SQLPrompt format it for readability). Your solution works. And works well. I use the exact same technique regularly. I recommend using ROW_NUMBER() instead of RANK() because it is always possible to get 2 identical rows -- at least it happened to me enough to cause me to change. But your original solution works well.

SELECT t.product_id
     , CASE
           WHEN t.change_date <= '2019-08-16' THEN
               t.new_price
           ELSE
               10
       END AS price
FROM
(
    SELECT product_id
         , new_price
         , change_date
         , ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY change_date DESC) AS rk
    FROM products
) t
WHERE t.rk = 1;

How to get the last sold price in SQL

If I understand correctly, you can use conditional aggregation:

select item_id, item_desc,
       sum(qty_shipped) as [Total Qty Shipped],
       count(item_id) as [No of times Shipped], 
       max(invoice_date) as Max_Date,
       max(case when seqnum = 1 then unit_price end) as [Last Price],
from (select shr.*,
             row_number() over (partition by item_id order by invoice_date desc) as seqnum
      from sales_history_report shr
     ) shr
where item_id = 1234 and
      year_for_period >= 2017 and
      sales_location_id like '10'
group by item_id, item_desc;

Comments:

• Do not use single quotes for column aliases. Only use single quotes for string and date constants.
• The columns in the GROUP BY define the rows in the result set. I don't think you want unit_price in it.
• Do not use single quotes for numeric constants. I assume item_id an the year are numeric.

How to Get Max Price Based on Latest Date From group by query

Extract date from your timestamp column latest_update and apply max() to get the latest date. Now, you can just add a where condition to filter rows only having this max date.

select name,
MAX(case WHEN source = 'att' THEN price ELSE 0 END) as att_price,
MAX(case WHEN source = 'sprint' THEN price ELSE 0 END) as sprint_price
FROM test 
where date(latest_update) = (select max(date(latest_update)) from test)
GROUP BY name;

Demo: https://www.db-fiddle.com/f/43Uy7ocCKQRqJSaYHGcyRq/0

Update:

Since you need group by for each source according to individual source latest_update column, you can use the below SQL:

select t1.name,
    max(
        case 
            when t1.source = 'att' then t1.price
            else 0
        end
      ) as att_price,
     max(
        case 
            when t1.source = 'sprint' then t1.price
            else 0
        end
      ) as sprint_price
from test t1
      inner join (
        select name,source,max(latest_update) as latest_update
        from test
        group by name,source) t2
      on t1.name = t2.name and t1.source = t2.source
      and t1.latest_update  = t2.latest_update
group by t1.name;

Demo: https://www.db-fiddle.com/f/qwBxizWooVG7AMwqKjewP/0

---

Related Topics