CODEWITHSUNDEEP
postgresqldistinct
thistleknot
thistleknot

Reputation: 1158

select distinct from multiple columns return multiple columns (all columns) PSQL

I'm trying to do a distinct operation on OHLC data where I have multiple dates per symbol

I can do the operation itself just fine, it only returns date, and symbol

select distinct timestamp, symbol from mv_qs_facts group by mv_qs_facts.symbol, mv_qs_facts.timestamp;

but I'd like it to return all columns (additional: close, open, high, low, volume) as well.

My goal is to return the last distinct (timestamp, symbol)

an idea I had.

select distinct on (timestamp, symbol), close, open, high, low from mv_qs_facts group by mv_qs_facts.symbol, mv_qs_facts.timestamp;

I see it's not as easy as this statement.

I've read I might be able to solve it with a subquery, a temporary table, or a join (all which don't use distinct).

Upvotes: 1

Views: 1033

Answers (3)

thistleknot
thistleknot

Reputation: 1158

I added id because I initially had timestamp and date as my composite key but it turned out to be bad with duplicate dates and I needed something to reference to sort by

CREATE materialized view temp AS  
SELECT DISTINCT ON (symbol, timestamp)
   id, timestamp, symbol, close, open, high, low
FROM qs_facts order by symbol, timestamp, id desc;

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 248135

Use DISTINCT ON ():

SELECT DISTINCT ON (timestamp, symbol)
   timestamp, symbol, close, open, high, low
FROM mv_qs_facts;

This will return close, open, high and low for a random member of the group.

If you want to control which member is used, add an ORDER BY clause, then the first member in this ordering is taken.

If the problem is memory consumption on the client, you should use cursors:

BEGIN;
DECLARE c CURSOR FOR SELECT ...;
FETCH 100 FROM c;
FETCH 100 FROM c;
...
COMMIT;

Upvotes: 2

Ed Bangga
Ed Bangga

Reputation: 13026

Here's your query.

select distinct t1.* from (
select row_number() over (partition by symbol order by timestamp desc) as rn, * from 
mv_qs_facts) as t1
    where t1.rn = 1

Upvotes: 0

Related Questions