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 wantunit_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
Inner Join with Count() on Three Tables
Differences Between "Foreign Key" and "Constraint Foreign Key"
Difference Between Varchar(500) VS Varchar(Max) in SQL Server
Query Combinations with Nested Array of Records in JSON Datatype
Postgresql Select Until Certain Total Amount Is Reached
Workaround for Ora-00997: Illegal Use of Long Datatype
Left Outer Join and an Additional Where Clause
Check Constraint on Date of Birth
How to Insert with Where Clause
Instead of Null How to Show '0' in Result with Select Statement SQL
Oracle Db: How to Write Query Ignoring Case
Bulk Insert with Variable File Name
Casting Scientific Notation (From Varchar -> Numeric) in a View
How to Combine Two Rows and Calculate the Time Difference Between Two Timestamp Values in MySQL
SQL Oracle Left Join and Subquery Error: Ora-00905: Missing Keyword