How to use mysql to get first/least/max row per group

This is really important if you need to latest or oldest entry when there are few matching entries on the table. See the use case below




As you can see there are multiple entries per given BC_TENANT_ID please note that BC_ID is auto increment field. let say you need to get latest entry for that paticular
BC_TENANT_ID.So ideallt we can do this by following way.
1. select all entries per given BC_TENANT_ID 
2.Then get the latest BC_ID within them
3.continue this for all BC_TENANT_ID s


 below query will do this
select f.BC_ID, f.BC_TENANT_ID, BC_FILTER from (select BC_TENANT_ID, MAX(BC_ID) as minID from BC_SUBSCRIPTION group by BC_TENANT_ID
) as x inner join BC_SUBSCRIPTION as f on f.BC_TENANT_ID = x.BC_TENANT_ID and f.BC_ID = x.minID;


See the below results
 
so you will get latest entry per each BC_TENANT_ID
If you need to do further filtering you can do it following way
 if you need to get latest entry per  BC_TENANT_ID with BC_FILTER = 'Demo' and BC_IS_ACTIVE='0'
use following query


select f.BC_ID, f.BC_TENANT_ID, BC_FILTER from (select BC_TENANT_ID, MAX(BC_ID) as minID from BC_SUBSCRIPTION group by BC_TENANT_ID
) as x inner join BC_SUBSCRIPTION as f on f.BC_TENANT_ID = x.BC_TENANT_ID and f.BC_ID = x.minID and f.BC_FILTER = 'Demo' and f.BC_IS_ACTIVE='0';


Results will look like this


change the query as you need and use it.

No comments:

Post a Comment

Empowering the Future of API Management: Unveiling the Journey of WSO2 API Platform for Kubernetes (APK) Project and the Anticipated Alpha Release

  Introduction In the ever-evolving realm of API management, our journey embarked on the APK project eight months ago, and now, with great a...