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