Select latest record from recordset using SQL and LINQ

Problem : How to select latest order from a list of order collection based on date using LINQ and SQL

SQL Table Order

SQL Query

select T1.id, T1.quantity from [order] T1
inner join (select ID, Max(created) as latest from [order] group by ID) T2
ON (T1.ID = T2.ID and T1.created = T2.latest)

LINQ

class Order
{
public int ID { get; set; }
public int Quantity { get; set; }
public string Created { get; set; }
}

List<Order> Orders = new List<Order>();
Orders.Add(new Order() { ID = 1, Quantity = 10, Created = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff") });
Thread.Sleep(1);
Orders.Add(new Order() { ID = 1, Quantity = 10, Created = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff") });
Thread.Sleep(1);
Orders.Add(new Order() { ID = 2, Quantity = 12, Created = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff") });
Thread.Sleep(1);
Orders.Add(new Order() { ID = 1, Quantity = 15, Created = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff") });

List FinalOrders = (from a in Orders group a by a.ID into latest
join b in Orders on new { ID = latest.Key, dt = latest.Max(itm => itm.Created) } equals new { ID = b.ID, dt = b.Created }
select new Order {ID = b.ID, Quantity = b.Quantity}).ToList();

GROUP BY, GROUPING SETS, ROLLUP and CUBE

I got a chance to look into some of the SQL server functions and I will say it is really useful specially for those who working in any kind of reporting like SSRS(SQL server reporting service) and also it is useful for all other SQL developers. Let us have look at these functions and  all these function are inter linked in terms of the way we want different types of results. Each functions are used to prepare some kind of summary results.

 

Before you I go directly with each function I can give you table view where I have tested all these functions.
I have used two tables to create an example with order and purchasing items for same.

 

GROUP BY and  GROUPING SETS

I do not need to explain much about GROUP BY since everybody knows well but when we combine GROUPING SETS with GROUP BY it is quiet different and gives a summary result on top of GROUP BY. So basically if some body want to find a total amount at each order or purchase level, this will be the ideal function. Please see below the query and results

SELECT oh.OrderID ,oh.ProductID ,SUM(p.price) AS Total FROM OrderHistory oh 
inner join product p on oh.productid = p.productid GROUP BY GROUPING SETS(oh.OrderID,oh.ProductID)

ROLLUP

ROLLUP is again going one more level into the summary . for eg : if we want to display Order and items purchased for that order and also total order.

SELECT COALESCE(cast(oh.OrderID as varchar(50)),'Grand : ') as 'Order', 
COALESCE(p.Name,'Total : ') as Product ,SUM(p.price) AS Total FROM OrderHistory oh 
inner join product p on oh.productid = p.productid GROUP BY  oh.OrderID,p.Name WITH ROLLUP

CUBE

 CUBE is again going one more level into the summary . for eg : if we want to display Order and each items purchased for that order and also total order.

SELECT oh.OrderID as ‘Order’, COALESCE(p.Name,‘Total : ‘) as Product, SUM(p.price) AS Total FROM OrderHistory oh inner join product p on oh.productid =p.productid GROUP BY oh.OrderID, p.Name WITH CUBE

 

happy reading and please let us know if anybody have a better approach than this or any other easy and optimized way to achieve the same summary results.