I have two tables as follows.
Table 1:
| ID | ArtNumber | Date | Price |
|---|---|---|---|
| 1 | Article1 | 07.05.2022 | 100.00 |
| 2 | Article2 | 07.05.2022 | 100.00 |
| 3 | Article1 | 10.05.2022 | 100.00 |
| 4 | Article2 | 10.05.2022 | 100.00 |
Table 2
| ID | ArtNumber | Price |
|---|---|---|
| 1 | Article1 | 80.00 |
| 2 | Article2 | 120.00 |
I want for Table 1 the newest (Date) price for each ArtNumber. But also want to then check if the price is more than in Table 2. (Table1.Price<Table2.Price)
Expected result:
| ArtNumber | Price |
|---|---|
| Article2 | 120.00 |
I have tried a lot of GROUP BY, ORDER BY, MAX() and DISTINCT combinations without success.
CodePudding user response：
You can use a simple greatest-n-per-group query:
with cte as (
select *, row_number() over (partition by artnumber order by date desc) as rn
from table1
)
select *
from cte
join table2 on cte.artnumber = table2.artnumber
where rn = 1 and cte.price < table2.price