CODEWITHSUNDEEP
sqlfirebirdgreatest-n-per-group
zac
zac

Reputation: 4918

How to get last recent value only

I have two tables master table1 and detail table2

Table1 structure

table1_id integer,
description varchar(50)

Table2 structure

table2_id integer,
table1_id integer,
price numeric(7,2),
Price_date date

I need to write SQL or a view that link between these two tables but the problem I want from table2 only to get a single value that is the most recent one of the price field using the price_date field to be linked with table1, in other words I dont want to get any duplicates from Table2.table1_id field. how I should write that SQL ?

Upvotes: 0

Views: 97

Answers (1)

Mark Rotteveel
Mark Rotteveel

Reputation: 109136

I assume you want all the latest entries for each table1_id from the table, and not just the latest entry (which is a lot simpler).

If you want the latest per table1_id, you first need to select the newest record per id, and then query the rest of the record, so something like this:

WITH latestprice (
    SELECT table1_id, MAX(price_date) AS price_date
    FROM table2
    GROUP BY table1_id
)
SELECT t2.table2_id, t2.table1_id, t2.price, t2.price_date
FROM latestprice l
INNER JOIN table2 t2
    ON t2.table1_ud = l.table1_id AND t2.price_date = l.price_date

If you also want information from table1, then you need to add an extra join. Note that the above query has one flaw: if there are multiple entries for table1_id with the same price_date it will produce multiple rows and duplicates per table_id. If you want to enforce that this can't happen, you need to add an unique constraint for table1_id, price_date.

Upvotes: 1

Related Questions