Using group by with order in postgresql

Query:

select "fightEventId", fe."from" , f.status
from 
    location_time_slot lts 
inner join
    fight_events fe on lts."fightEventId" = fe.id
inner join
    fights f on lts.id = f."slotId"
where
    f.status = 'CONFIRMED'
    and true 
    and (
        now() at time zone 'utc' >= fe."from" and now() at time zone 'utc' <= fe."to" 
    )
    or true and now() at time zone 'utc' <= fe."to" 
    or false and now() at time zone 'utc' > fe."to"
order by fe."from" 
limit 100
offset 0;

It yields this:

fightEventId|from               |status   |
------------+-------------------+---------+
           2|2021-07-02 15:00:00|CONFIRMED|
           2|2021-07-02 15:00:00|CONFIRMED|
           2|2021-07-02 15:00:00|CONFIRMED|
           2|2021-07-02 15:00:00|CONFIRMED|
          17|2021-07-03 15:00:00|CONFIRMED|
          17|2021-07-03 15:00:00|CONFIRMED|
           1|2021-07-03 15:00:00|CONFIRMED|
           1|2021-07-03 15:00:00|CONFIRMED|
           1|2021-07-03 15:00:00|CONFIRMED|
           1|2021-07-03 15:00:00|CONFIRMED|
           1|2021-07-03 15:00:00|CONFIRMED|
           1|2021-07-03 15:00:00|CONFIRMED|
           1|2021-07-03 15:00:00|CONFIRMED|
           1|2021-07-03 15:00:00|CONFIRMED|
           1|2021-07-03 15:00:00|CONFIRMED|
           1|2021-07-03 15:00:00|CONFIRMED|
           1|2021-07-03 15:00:00|CONFIRMED|
           1|2021-07-03 15:00:00|CONFIRMED|
           1|2021-07-03 15:00:00|CONFIRMED|
           1|2021-07-03 15:00:00|CONFIRMED|

What I need is to show those "fightEventId"s that have more than 2 CONFIRMED fights. Currently all records are ordered in the right way, so the result I expect is:

fightEventId|
------------+
           2|
           1|

How do I do this? Every time I try, I get errors or the order is broken.

As for the DBMS used in the project, it’s PostgreSQL but I wrote MySQL cos I’m pretty sure there’re solutions that work with both.

P.S.

This query comes from the ORM I’m using, so please don’t be confused with those trues and falses. 🙂

Answer

You would just use aggregation and having:

select fightEventId
from t
where status = 'CONFIRMED'
group by fightEventId
having count(*) > 2;

In your query:

select fightEventId 
from location_time_slot lts join
     fight_events fe
     on lts."fightEventId" = fe.id join
     fights f on lts.id = f."slotId"
where f.status = 'CONFIRMED' and
      (now() at time zone 'utc' >= fe."from" and now() at time zone 'utc' <= fe."to" 
      ) or
      now() at time zone 'utc' <= fe."to" or
      now() at time zone 'utc' > fe."to"
group by fightEventId
having count(*) > 2
order by max(fe."from");

Advertisement

Answer