CODEWITHSUNDEEP
postgresqljoinleft-join
KuKu
KuKu

Reputation: 646

excluding rows from resultset in postgres

This is my result set

resultset

i am returning this result set on the base of refid using WHERE refid IN. Over here, i need to apply a logic without any kind of programming (means SQL query only).

if in result set, i am getting period for particular refid then other rows with the same refid must not returned.

for example, 2667105 having period then myid = 612084598 must not get returned in result set.

according to me this can be achieved using CASE but i have no idea how to use it, i mean that i don't know should i use the CASE statement in SELECT statement or WHERE clause...

EDIT:

This is how it suppose to work,

myid = 612084598 is the default row for refid = 2667105 but if specifically wants the refid for period = 6 then it must return all rows except myid = 612084598

but if i am looking for period = 12, for this period no specific refid present in database.. so for this it must return all rows except first one.. means all rows with the refid which is default one..

Upvotes: 0

Views: 823

Answers (1)

roman
roman

Reputation: 117530

Not very clear definition of the problem, but try this:

with cte as (
    select
        *,
        first_value(period) over(partition by refid order by myid) as fv
    from test
)
select
    myid, refid, period
from cte
where period is not null or fv is null

sql fiddle demo

Upvotes: 2

Related Questions