I seek to find the maximum timestamp (ob.create_ts) for each group of marketid's (ob.marketid), joining tables obe (ob.orderbookid = obe.orderbookid) and market (ob.marketid = m.marketid). Although there are a number of solutions posted like this for a single table, when I join multiple tables, I get redundant results. Sample table and desired results below:

table: ob

table: obe

table: m

desired results

CodePudding user response：

Use ROW_NUMBER() to get a properly filtered ob table. Then JOIN the other tables onto that!

WITH
    ob_filtered AS (
        SELECT
            orderbookid,
            marketid
        FROM
            (
            SELECT
                *,
                ROW_NUMBER() OVER (
                    PARTITION BY
                        marketid
                    ORDER BY
                        create_ts DESC
                ) AS create_ts_rownumber
            FROM
                ob
            ) ob_with_rownumber
        WHERE
            create_ts_rownumber = 1
        )

SELECT
    ob_filtered.orderbookid,
    ob_filtered.marketid,
    obe.orderbookentryid,
    obe.entryname,
    m.marketname
FROM
    ob_filtered
    JOIN m 
        ON m.marketid = ob_filtered.marketid
    JOIN obe 
        ON ob_filtered.orderbookid = obe.orderbookid
;