select rows based on another query result with rank PostgreSQL



  • 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



  • 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

Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2