CODEWITHSUNDEEP
sql
Ben
Ben

Reputation: 13

Select latest entry using date field (no repeats)

I have a really simple query which returns a list of item numbers, the date they were entered into the system, and the date when the entry was last modified:

SELECT DISTINCT asset_id, entered_date, modified_date
FROM price_data

The issue is that occasionally items are priced more than once, resulting in entries that have the same asset_id and entered_date, but different modified_dates. The above query works in that it returns the prices, but it returns both the original entry and the latest entry for anything that is repriced. How can I make this query return only the latest price value rather than both for any items that have been repriced?

Any help would be greatly appreciated! Many thanks.

Upvotes: 0

Views: 58

Answers (2)

Brian DeMilia
Brian DeMilia

Reputation: 13238

select p.*
  from price_data p
  join (select asset_id, max(modified_date) as last_modified_date
          from price_data
         group by asset_id) v
    on v.last_modified_date = p.modified_date

You can't group by price without impacting the results, so you have to select the latest modified date separately in an inline view and then join back to the actual table.

Upvotes: 0

juergen d
juergen d

Reputation: 204884

You can group by the columns you want to be unique and thenselect for each group the highest modified_date

SELECT asset_id, entered_date, max(modified_date)
FROM price_data
GROUP BY asset_id, entered_date

Upvotes: 1

Related Questions