select rows based on another query result with rank PostgreSQL

Question

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

• Brendan McCaffrey: Can you provide a sample of data in said tables, as well as the expected output of the query?
• Uzay: Updated!. Just trying to get results from another table with handled query ids

Answer 1

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 Fiddle.

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

• Uzay: Thank you question updated
• Brendan McCaffrey: I believe this is returning your expect result. Can you confirm?
• Uzay: I already tried this and others. query returning older data. query must return words which related with first query
• Brendan McCaffrey: It's returning the same output as what you have listed in your question as the "expected" output. If that is not the expected output, can you update your question with what is the expected output?
• Uzay: re check my data and found some older data with related query thank you