I have two tables A and B. The common key between the two tables is "key". I am joining the two tables to get the following table: ID, Date, Value. This is the code I use:
SELECT DISTINCT B.ID, A.Date, A.Value
FROM A
INNER JOIN B ON A.key = B.key
For a certain ID it is ok to have multiple dates but these dates should be different. When the same date appears more than once, a different column called "Date2" should be checked in table A and select only the value with the most recent "Date2"
Example from Table A:
Date | Date2 | Value |
---|---|---|
01-01-2000 | 01-02-2000 | 20 |
01-01-2000 | 01-03-2003 | 40 |
In the final table only the second line should be present because the Date2 value is more recent.
I am not sure how to go about with this. Any help is much appreciated.
>Solution :
If your DBMS supports window functions, you should use FIRST_VALUE
on both "B.ID" and "A.Value", while:
- partitioning on "Date"
- ordering on "Date2" descendently
This will ensure that each "A.Date" will have only one record, whose "B.ID" and "Value" will be the highest possible.
SELECT DISTINCT
FIRST_VALUE(B.ID) OVER(PARTITION BY A.Date ORDER BY A.Date2 DESC) AS ID,
A.Date,
FIRST_VALUE(A.Value) OVER(PARTITION BY A.Date ORDER BY A.Date2 DESC) AS Value
FROM A
INNER JOIN B ON A.key = B.key
If your ID is the same across the same "A.Date", you can replace the first window function with only "B.ID".