darkpool darkpool - 4 months ago 14
SQL Question

Using WHERE clause and DISTINCT ON

I have the following two postgresql tables:

table: daily

id date close symbol_id
1 2016-05-01 80 65
2 2016-05-01 75 67
3 2016-05-01 95 45
4 2016-05-02 11 65
5 2016-05-02 48 67
6 2016-05-02 135 45
7 2016-05-03 18 65
8 2016-05-03 82 67
9 2016-05-03 107 45
10 2016-05-04 29 65

table: symbol

id symbol
65 abc
67 xyz
45 jkl


I need to select all
symbols
where the
close
value is less than 100 for the latest date for each symbol. As per the example, not all symbols will have the same latest date.

The following query gives me correct data when I do not use the
WHERE
clause:

SELECT DISTINCT ON (daily.symbol_id) symbol.symbol, daily.close, daily.date
FROM daily JOIN symbol ON daily.symbol_id = symbol.id
--WHERE daily.close < 100
ORDER BY daily.symbol_id, daily.date DESC

Result:

symbol close date
abc 29 2016-05-04
xyz 82 2016-05-03
jkl 107 2016-05-03


The problem comes when I uncomment the
WHERE
clause. The desired result is for the symbol
jkl
to be removed from the list because the value for
close
for that symbol on its latest date is not < 100. However this is what happens:

symbol close date
abc 29 2016-05-04
xyz 82 2016-05-03
jkl 95 2016-05-01

Answer

You can move your existing query to a subquery and then filter with where criteria.

select * 
from (
    select distinct on (d.symbol_id) s.symbol, d.close, d.date
    from daily d 
        join symbol s on d.symbol_id = s.id
    order by daily.symbol_id, daily.date desc
) t
where close < 100

Here's another similar option using a windows function such as row_number:

select *
from (
    select d.symbol_id, s.symbol, d.close, d.date,
           row_number() over (partition by d.symbol_id order by d.date desc) rn
    from daily d 
        join symbol s on d.symbol_id = s.id
    ) t
where rn = 1 and close < 100