SQL query for fetching attributes of the current entity from a table magazine type

There is a table that stores attributes of entities, such as a product identifier, price and date of the last update. When you add new prices to the table, a row is inserted ('id-product', 'new price', 'add'). How to obtain a sample with current prices for all goods?
October 14th 19 at 10:31
2 answers
October 14th 19 at 10:33
Solution
select id, price, date_modified
from prices
where id, date_modified in (
select id, max(date_modified)
from prices
group by id
)

And if you have Oracle, it is possible without explicit subquery:

select distinct id, first_value(price) over (partition by id order by date_modified desc)
from prices
A small correction. The first query should be this (forgot brackets):

select id, price, date_modified
from prices
where (id, date_modified) in (
select id, max(date_modified)
from prices
group by id
) - judah.Quitzon commented on October 14th 19 at 10:36
October 14th 19 at 10:35
It is possible without a subquery:

select id, price, date_modified from prices
group by id
having date_modified = max(date_modified)
Not a GROUP BY expression. Under Oracle clearly is not working. - judah.Quitzon commented on October 14th 19 at 10:38
Also doubt that will work in Mysql. - judah.Quitzon commented on October 14th 19 at 10:41
In mysql working 100% - Jovani.Barton83 commented on October 14th 19 at 10:44
Yeah checked it myself, really works. Not doing so simply never:), I know now. - nigel.Nitzsche commented on October 14th 19 at 10:47
With MySQL do not work, but, apparently, it is somewhat softer refers to the SQL standards =) - Jovani.Barton83 commented on October 14th 19 at 10:50
I think the problem with "Not a GROUP BY expression" can be solved by using join with the result of the query:
SELECT p.* FROM prices AS p JOIN
 (SELECT id FROM prices
 GROUP BY id
 HAVING date_modified = MAX(date_modified)) AS a
 ON p.id = a.id
- judah.Quitzon commented on October 14th 19 at 10:53

Find more questions by tags SQL