1

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

2
  • Can you provide a sample of data in said tables, as well as the expected output of the query? Feb 28, 2022 at 12:13
  • Updated!. Just trying to get results from another table with handled query ids
    – Uzay
    Feb 28, 2022 at 12:41

1 Answer 1

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
7
  • Thank you question updated
    – Uzay
    Feb 28, 2022 at 13:49
  • I believe this is returning your expect result. Can you confirm? Feb 28, 2022 at 14:20
  • I already tried this and others. query returning older data. query must return words which related with first query
    – Uzay
    Feb 28, 2022 at 14:26
  • 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? Feb 28, 2022 at 14:29
  • 1
    re check my data and found some older data with related query thank you
    – Uzay
    Feb 28, 2022 at 15:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.