Monday, November 14, 2011

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.

Wednesday, November 2, 2011

Some usefull windows commands

If you need to get installed windows version and service pack name from command line use following command.

C:\>systeminfo | findstr /B /C:”OS Name” /C:”OS Version”

Then you will see output as follows
OS Name: Microsoft Windows XP Professional
OS Version: 5.1.2600 Service Pack 2 Build 2600