CODEWITHSUNDEEP
mysqlsqlgreatest-n-per-group
crmepham
crmepham

Reputation: 4760

How to get the earliest of each type in MySQL

For the following data:

mysql> select * from policy_redeem_window;
+----+---------+-----------+----------+-------+---------------------+---------+---------------------+
| id | user_id | policy_id | delta_id | value | start_date          | state   | created             |
+----+---------+-----------+----------+-------+---------------------+---------+---------------------+
|  1 |       0 | policy1   | delta1   |  1.00 | 2019-12-11 14:22:21 | PENDING | 2019-12-11 14:22:21 |
|  2 |       0 | policy1   | delta2   |  1.00 | 2019-12-12 14:22:33 | PENDING | 2019-12-11 14:22:33 |
|  3 |       0 | policy2   | delta3   |  1.00 | 2019-12-11 14:22:45 | PENDING | 2019-12-11 14:22:45 |
|  4 |       0 | policy2   | delta4   |  1.00 | 2019-12-12 14:23:08 | ACTIVE  | 2019-12-11 14:23:08 |
|  6 |       0 | policy2   | delta5   |  1.00 | 2019-12-11 14:23:37 | ACTIVE  | 2019-12-11 14:23:37 |
+----+---------+-----------+----------+-------+---------------------+---------+---------------------+
5 rows in set (0.00 sec)

I am attempting to get just a single row per policy_id where each row is the row with the earliest start_date:

I have tried the following query:

select * 
from policy_redeem_window 
where user_id = 0 
and state in ('ACTIVE', 'PENDING') 
group by policy_id 
order by start_date desc;

But this gives me the following error:

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'admiral.policy_redeem_window.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Where am I going wrong here?

Upvotes: 0

Views: 54

Answers (2)

forpas
forpas

Reputation: 164154

With NOT EXISTS:

select p.* from policy_redeem_window p
where p.user_id = 0 
and p.state in ('ACTIVE', 'PENDING')
and not exists (
  select 1 from policy_redeem_window 
  where policy_id = p.policy_id and user_id = p.user_id and state in ('ACTIVE', 'PENDING') 
  and start_date < p.start_date 
);

See the demo.
For MySql 8.0+ with ROW_NUMBER():

select p.id, p.user_id, p.policy_id, p.delta_id, p.value, p.start_date, p.state, p.created
from (
  select *, row_number() over (partition by policy_id order by start_date) rn
  from policy_redeem_window
  where user_id = 0 and state in ('ACTIVE', 'PENDING')
) p
where p.rn = 1;

See the demo.
Results:

| id  | user_id | policy_id | delta_id | value | start_date          | state   | created             |
| --- | ------- | --------- | -------- | ----- | ------------------- | ------- | ------------------- |
| 1   | 0       | policy1   | delta1   | 1     | 2019-12-11 14:22:21 | PENDING | 2019-12-11 14:22:21 |
| 3   | 0       | policy2   | delta3   | 1     | 2019-12-11 14:22:45 | PENDING | 2019-12-11 14:22:45 |

Upvotes: 1

Salman Arshad
Salman Arshad

Reputation: 272276

One common workaround is to join with an aggregate, this allows you to fetch full rows for each group:

SELECT t.*
FROM (
    SELECT policy_id, MIN(start_date) AS first_date
    FROM policy_redeem_window
    WHERE user_id = 0
    AND state IN ('ACTIVE', 'PENDING')
    GROUP BY policy_id
) AS g
JOIN policy_redeem_window AS t ON g.policy_id = t.policy_id AND g.first_date = t.start_date

Upvotes: 1

Related Questions