pythonsqlpypika

How to create SQL Pypika Query with "Min()"

I am trying to create a Pypika Query which uses the MIN('') function of SQL. Pypika supports the function but I don't know how to use it.

Basically I want to create this SQL statement in Pypika:

select 
"ID","Car","Road","House"
  from "thingsTable"
 where "ID" not in
 (
 select MIN("ID")
 from "thingsTable"
 GROUP BY 
"Car","Road","House"
)
order by "ID"

I have tried something like this:

from pypika import Query, Table, Field, Function

query = Query.from_(table).select(min(table.ID)).groupby(table.Car, table.Road, table.House)

And variations of it, but can't figure out how to use this function. There are not a lot of examples around.

Thanks in advance.

Solution

  • Try this one

    the code based on Selecting Data with pypika

    from pypika import functions as fn

tbl = Table('thingsTable')
q = Query.from_(tbl).where(
    tbl.ID.isin(tbl.groupby(tbl.Car, tbl.Road, tbl.House).select(fn.Min(tbl.Id)))
).select(
    tbl.Id,tbl.Car,tbl.House,tbl.Road
).orderby(tbl.Id)