I have a query with more than one table with a lot of criteria that gives this result
select book, max(version)
from a,b
where condition 1, condition 2... and so on
the query is nice , the result is
| book | version |
|---|---|
| book1 | 3 |
| book2 | 2 |
| book3 | 1 |
I want to join this result with another table c:
| book | version | id |
|---|---|---|
| book1 | 1 | id1 |
| book1 | 2 | id2 |
| book1 | 3 | id3 -> I want to join the result with this row |
| book1 | 4 | id4 |
| book2 | 1 | id5 |
| book2 | 2 | id6 -> I want to join the result with this row |
| book3 | 1 | id7 -> I want to join the result with this row |
CodePudding user response：
If you need to join Table A and Table C. than try below query:
SELECT a.book, max(version) over
( partition by a.book order by a.book) version,c.id
FROM tablea a, tablec c
where a.book.c.book
and a.version=c.version;
CodePudding user response：
Use a sub-query and join:
SELECT m.book,
c.version,
c.id
FROM (
select book,
max(version) AS max_version
from a INNER JOIN b
ON (condition 1)
where condition 2 ... and so one
) m
INNER JOIN c
ON ( m.book = c.book
AND m.max_version = version )