CODEWITHSUNDEEP
sqlpostgresqlselect
Patrick Chong
Patrick Chong

Reputation: 61

How can I query each day of a SQL table and check if a particular field value appears in two distinct rows?

I have a Postgres SQL table that looks like the following:

The index_constituents table:

id        date      opening_closing      ISIN 

1       2016-05-03        O              AAAAAA
2       2018-04-03        C              ABDFGJ
5       2020-01-02        C              HIFHSA
10      2024-01-01        C              FOHOAS
111     2022-05-05        O              JIOFHS
15      2016-05-03        C              AAAAAA
16      2018-04-03        O              ABDFGJ
20      2020-01-02        C              HIFHSA

....

The opening_closing field will only have vals of O and C.

The real table has over 13k rows and something like 2k dates in total.

What I am trying to do is check for each date. If the ISIN appears in both the O and C rows. If it appears in one but not the other, then I would like to return the ISIN.

So In the example above, ISIN=AAAAA appears in id 1 and 15, where row 1 has O and row 15 has C, so that ISIN is okay and I DO NOT want it to be returned.

Whereas ISIN=FOHOAS only appears in a C row (id=10) but does not appear in a row with O, so I would like it to be returned in the final result.

The final result would just be all the ISINs that appear in either a C row or O row but not both.

For the above example, I would expect FOHOAS and JIOFHS to be returned only.

Upvotes: 2

Views: 57

Answers (3)

GuidoG
GuidoG

Reputation: 12059

Another option is to exclude unwanted rows by a not exists

In this case we only want those rows that have a matching date/isin pair with another value for opening_closing. The not exists exludes all rows that do not match that

select distinct i.isin
from   index_constituents i
where not exists ( select *
                   from   index_constituents i2
                   where  i2.myDate = i.myDate
                   and    i2.isin = i.isin
                   and    i2.opening_closing <> i.opening_closing
                 )

See a demo here of 3 possible solutions

isin
FOHOAS
JIOFHS
HIFHSA

And yet another option is to only take those rows that have only one count

In this case we don't care about the value in opening_closing we only check that the combination date/isin appears only once

select distinct i.isin
from   index_constituents i
where  1 = ( select count(*)
             from   index_constituents i2
             where  i2.myDate = i.myDate
             and    i2.isin = i.isin
           )

that would give this result

isin
FOHOAS
JIOFHS

See a demo here

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522509

Here is a left anti-self-join approach:

SELECT ic1."date", ic1.ISIN
FROM index_constituents ic1
LEFT JOIN index_constituents ic2
    ON ic2."date" = ic1."date" AND
       ic2.ISIN = ic1.ISIN AND
       ic2.opening_closing <> ic1.opening_closing
WHERE
    ic2."date" IS NULL;

Demo

Result set:

date       | isin
2020-01-02 | HIFHSA
2024-01-01 | FOHOAS
2022-05-05 | JIOFHS
2020-01-02 | HIFHSA

The idea here is to try to match each date-ISIN pair to another record in the same table having a different value for opening/closing. The null check in the WHERE clause would only be true for those pairs not having 2 different opening/closing values.

Upvotes: 0

Mureinik
Mureinik

Reputation: 312076

You could group by the date and the isin, and then count the distinct number opening_closing values it has:

SELECT   date, isin
FROM     index_constituents
GROUP BY date, isin
HAVING   COUNT(DISTINCT opening_closing) < 2

Upvotes: 2

Related Questions