[postgis-users] Discrepancy in result

[Marcin Mionskowski]

Do you mean, that non-restricted result has only 71 rows, or only 71 rows in your period?
If 2nd, than I think i'ts becouse of "distinct on".
You are geting one row per st.gid.
When run agains whole set, it will give you "first found" row per gid with whatever timing assigned.
When you restrict the timing, only timings from your period can be assigned.


On Thu, 22 Feb 2018 17:39:40 +0100, Lahari Sengupta <jhinik8 at gmail.com> wrote:

> My following query gives output of 72 rows which is correct.
>
> SELECT distinct on (st.gid) sv.timing, st.gid, st.areaFROM servers
> svINNER JOIN sites stON (st_distance(sv.geom, st.geom)<8.5) where
>         sv.workmode = 1 AND sv.timing >= timestamp '2018-02-02 00:00:00'
>         and sv.timing <= timestamp '2018-02-02 23:59:59';
>
> But if I remove the condition on timing and want to find out whole output,
> it misses one row which is between this time. Why is it happening? What is
> wrong with my query?
>
> SELECT distinct on (st.gid) sv.timing, st.gid, st.area
>     FROM servers sv
>     INNER JOIN sites st
>     ON (st_distance(sv.geom, st.geom)<8.5) where
>             sv.workmode = 1;