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.