CODEWITHSUNDEEP
sqldatabaseflasksqlalchemyflask-sqlalchemy
mmk
mmk

Reputation: 1

Select rows that do NOT have a DISTINCT value in SQLALCHEMY

Can someone suggest a method for selecting rows that do not have a unique value for a column? I don't have an active project, it's more like a question in my mind. So I don't have a db schema to share.

For example: If I have five records, with 1 record that has CustNo = 7, 1 record that has CustNo = 9, and three records that have CustNo = 11, I only want to select the three rows that have CustNo = 11

Upvotes: 0

Views: 50

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123429

You could use a SQL query like

SELECT * FROM invoice
WHERE CustNo IN (
    SELECT CustNo FROM invoice
    GROUP BY CustNo
    HAVING COUNT(*) > 1
)

which in SQLAlchemy's SQL Expression Language would be

qry = invoice.select().where(
    invoice.c.CustNo.in_(
        select([invoice.c.CustNo])
        .group_by("CustNo")
        .having(func.count(text("*")) > 1)
    )
)

Upvotes: 1

Related Questions