select rows based on another query result with rank PostgreSQL

[Cleofas]

I am beginner of SQL language.

I am trying to write a query that gets rows from words table based on result of another query (news table).

I have the below two tables.

News

id,	url	url_order	source	created
1	http://..	1	src1	2022-02-26 02:13:32.459176+03
2	http://..	1	src1	2022-02-26 02:12:32.459176+03
3	http://..	1	src1	2022-02-26 02:11:32.459176+03
4	http://..	2	src2	2022-02-26 02:10:36.683408+03
5	http://..	2	src2	2022-02-26 02:09:36.683408+03
6	http://..	2	src2	2022-02-26 02:08:36.683408+03
7	http://..	5	src6	2022-02-27 05:47:46.803674+03
8	http://..	5	src6	2022-02-27 05:46:46.803674+03
9	http://..	5	src6	2022-02-27 05:45:46.803674+03

Words

id,	news_id	word	source	created
1	1	foo	src1	2022-02-26 02:13:32.459176+03
2	4	ipsum	src1	2022-02-26 02:13:32.459176+03
3	7	dolor	src1	2022-02-26 02:13:32.459176+03
4	8	sit	src2	2022-02-26 02:13:36.683408+03
5	9	amet	src6	2022-02-27 05:47:46.803674+03

I'm getting the below result after the following query.

SELECT id, url, url_order, created, source
FROM (
    SELECT
        id,url,url_order,created,source,
        RANK() OVER(PARTITION BY url_order, source ORDER BY COALESCE(created) DESC) 
        MyRank  -- Rank each (def, item) combination by "time"
    FROM news
    ) src
WHERE MyRank = 1
AND url_order < 25

Result from above query (most recent row each url order)

id,	url	url_order	source	created
1	http://..	1	src1	2022-02-26 02:13:32.459176+03
4	http://..	2	src2	2022-02-26 02:10:36.683408+03
7	http://..	5	src6	2022-02-27 05:47:46.803674+03

I want to get related words from words table with news_id which I getting with this SQL query

expected output

id,	news_id	word	source	created
1	1	foo	src1	2022-02-26 02:13:32.459176+03
2	4	ipsum	src1	2022-02-26 02:13:32.459176+03
3	7	dolor	src1	2022-02-26 02:13:32.459176+03

I am getting rows which most recent of each group with above query and I need to get words related with this Id's from another table words

[nishika]

I think this is what you're looking for. You can simply turn your current query into a subquery for the query against the Words table.

SELECT *
FROM words
WHERE news_id in (
    SELECT id
    FROM (
        SELECT
            id,url,url_order,created,source,
            RANK() OVER(PARTITION BY url_order, source ORDER BY COALESCE(created) DESC) 
            MyRank  -- Rank each (def, item) combination by "time"
        FROM news
    ) src
    WHERE MyRank = 1
    AND url_order < 25
)

Below is the output from https://dbfiddle.uk/?rdbms=postgres_14&fiddle=ffcc855614273b6f4a7c5120cabde743 .

id	news_id	word	source	created
1	1	foo	src1	2022-02-26 02:13:32.459176
2	4	ipsum	src1	2022-02-26 02:13:32.459176
3	7	dolor	src1	2022-02-26 02:13:32.459176