Two vendors write into ticks(symbol, ts, price, vendor, seq, ingested_at) and the same trade can appear more than once for a (symbol, ts). Keep one row per (symbol, ts), preferring the higher seq and, on equal seq, the earliest ingested_at. Give the SQL and say why SELECT DISTINCT cannot do it.

Two vendors write into ticks(symbol, ts, price, vendor, seq, ingested_at) and the same trade can appear more than once for a (symbol, ts). Keep one row per (symbol, ts), preferring the higher seq and, on equal seq, the earliest ingested_at. Give the SQL and say why SELECT DISTINCT cannot do it.

Approach: Rank the candidate rows inside each key with the whole preference written into the ORDER BY, then keep rank one and check what happens when the preference is incomplete.

Rank inside the key and keep rank one: SELECT * FROM (SELECT *, row_number() OVER (PARTITION BY symbol, ts ORDER BY seq DESC, ingested_at ASC) AS rn FROM ticks) t WHERE rn = 1. The PARTITION BY names the identity of a trade and the ORDER BY encodes the full preference, higher seq first with earliest arrival as the tie break, so the survivor is chosen by a deterministic tie break rather than by whichever row the engine happened to read first. SELECT DISTINCT cannot express this because it deduplicates on the entire selected column list, and the duplicate rows differ in vendor, seq and ingested_at, so every one of them survives. Projecting down to (symbol, ts) with DISTINCT drops the price, which is the column you needed. Postgres offers DISTINCT ON (symbol, ts) with the same ORDER BY as a shorter form of the identical plan. Add a third tie break on vendor or on the primary key when seq and ingested_at can both collide, otherwise two runs of the same query return different prices and any comparison of two loads reports a difference that is not real.

Follow-up: How do you make the same dedup incremental so a nightly job does not rescan the whole table?

Key concepts: row_number, partition key, deterministic tie break, distinct on.