"greatest-n-per-group" query howto

[trueqbit]

Is it possible to perform a "greatest-n-per-group" query? I guess this belongs to one of the most needed tasks...

In other words: having a table consisting of multiple results for items, I want to select a single result row per item, based on a condition like an aggregated value.

This is possible using a (self) join and filtering by aggregated value (in case it is unique), or using window functions.
Especially because I need a 'self join' I can't figure out how to do this using sqlite_orm.

My specific example, given this table:

rowid	itemid	some_date	flag

Using a (self) join:

select count(r.itemid)
from some_result r
inner join (
  select itemid,
    max(some_date) as max_date
  from some_result
  group by itemid
  ) wnd
  on wnd.itemid = r.itemid
    and r.some_date = wnd.max_date
-- other conditions
where r.flag = 1

Using window functions:

select count(itemid)
from (
  select itemid,
    flag,
    row_number() over (
      partition by itemid order by some_date desc, rowid desc
      ) as rank
  from some_result
  ) as wnd
where wnd.rank = 1
  -- other conditions
  and wnd.flag = 1

Also see those answers on SO:
SQL select only rows with max value on a column (traditional self join)
SQL select only rows with max value on a column (window function)
Select info from table where row has max date

[fnc12]

Hello @trueqbit . Unfortunately such queries cannot be implemented using sqlite_orm for now. The first needs INNER JOIN from SELECT feature implementation. The second needs window functions support and FROM from SELECT feature support.
To implement INNER JOIN from SELECT and FROM from SELECT we need to create a clear and intuitive API for this. If you have any ideas please share

[trueqbit]

With CTEs it is possible to realize the INNER JOIN with a subselect. See special example in the CTE branch.