mysql - explaination of dealing with latest records of groups in a table

I will be covering three problems here

1. Fetch Latest record of each group
2. Fetch N Latest records of each group
3. Delete all records of a group except the Latest ones.


Say we have a table Products as

First, We wish to write a query that fetches the latest product shipped by each supplier. After scratching head for a while, I could find two ways to do it.


1. Write a Nested Query:

Fetch the results grouped by SupplierId and for each group, pass the ProductID that is maximum for that group. Query for this would be.

select *
from Products as a
where ProductId = (select MAX(ProductId)
                                from Products as b
                                where a.SupplierID = b.SupplierID)
group by SupplierID


Below image shows the execution for this query.

2) Self Join:

Another way is to perform a left join of the Products Table with itself. We will have to place two conditions on join. First will  be on SupplierID, as we want to find the latest product by each supplier and second would be on ProductID with the constrain that first table's productID is lesser than second. Doing so, the maximum ProuductID in table 1 will not be matched to any in table 2.

For example, if we have 3 products for a supplier as
-----------------------------------
|ProductID | Supplier ID|
-----------------------------------
|       1         |      1      |
|       2         |      1      |
|       3         |      1      |
-----------------------------------
If we perform a self join with the conditions that their Supplier Ids are equal and first table's ProductID is lesser than second , we will get
---------------------------------------------------------------------------
| a.ProductID | a.SupplierID | b.productID | b.supplierid |
---------------------------------------------------------------------------
|        1           |         1            |        2           |       1      |
|        1           |         1            |        3           |       1      |
|        2           |         1            |        3           |       1      |
|        3           |         1            |      NULL    |    NULL     |
---------------------------------------------------------------------------
Now, If we place where clause saying that b.productID should be NULL, we will get the desired result.


select a.*
from Products as a left join Products as b on (a.SupplierID=b.SupplierID
                                                                        and a.ProductID < b.ProductID)
where b.ProductID IS NULL


Below image shows the execution for this query.

Fetch N Latest Records

Similarly, in order to fetch N latest records we can write

select a.*
from Products as a left join Products as b on (a.SupplierID=b.SupplierID
                                                                        and a.ProductID < b.ProductID)
group by b.ProductID
having count(*) < 2

Below image shows the execution for this query.

Delete records except latest in each Group

Continuing the above logic, query for deletion would be

delete
from Products  as a left join Products as b on (a.SupplierID=b.SupplierID AND a.ProductID < b.ProductID)
where b.ProductID IS NOT NULL

How to change timezone of server Fedora/Ubuntu/Linux

 ln -sf /usr/share/zoneinfo/Asia/Calcutta /etc/localtime


In place of Asia/Calcutta, select the time zone you want to set.

Mysql - alter a table column to make it auto increment

Mysql : alter a table to make one of its column auto increment.


Query :




mysql> ALTER TABLE  WAREHOUSE MODIFY COLUMN WarehouseId INT(11) AUTO_INCREMENT;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key



i.e. in order to define a column as auto increment it must be set as primary key.

mysql> ALTER TABLE  WAREHOUSE MODIFY COLUMN WarehouseId INT(11) AUTO_INCREMENT PRIMARY KEY;

Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

PHP - find biggest number in a array where array can also have nested arrays

PHP -Write a program to find biggest number in an array where array can also have nested arrays.

For example given an array like
Input : (1, 0, 6, 9, array(100, -1, 10, 7), 40, array(101, array(120, 240), 180), 200)

that can contain numbers and nested arrays containing numbers, we need to return the maximum number.

Output : 240


Program:


<?php

$inputArray = array(1, 0, 6, 9, array(100, -1, 10, 7), 40, array(101, array(120, 240), 180), 200);
echo findBiggest($inputArray);

function findBiggest($inputArray)
{
        static $biggest = 0;
        if(is_array($inputArray))
        {
                foreach($inputArray as $arr)
                {
                        findBiggest($arr);
                }
        }
        else
        {
                if($inputArray > $biggest)
                        $biggest = $inputArray;
        }
        return $biggest;

}