Filter SQL data to get the latest information for each alternate identifier using MSSQL

Question

I want to filter the data in my SQL so that I only have the last row for each thickness where the IsReady field is true.

EDIT: I use the MSSQL 2008 server for anyone who needs to know

My data is as follows:

+--+----------+-----------+----------+-------+ |ID|SupplierID|ThicknessID|DateTime |IsReady| +--+----------+-----------+----------+-------+ |01|1 |1 |01/01/1990|1 | |02|1 |1 |01/01/2012|0 | |03|1 |2 |01/01/1990|1 | |04|1 |2 |01/01/2012|1 | 

Based on this data, the SQL code should return the following:

 +--+----------+-----------+----------+-------+ |ID|SupplierID|ThicknessID|DateTime |IsReady| +--+----------+-----------+----------+-------+ |01|1 |1 |01/01/1990|1 | |04|1 |2 |01/01/2012|1 | 

I hope you have enough information so that you can understand, if not just a comment

Answer 1

 SELECT t.* FROM Table t WHERE IsReady=1 AND t.ID=(SELECT TOP 1 t2.ID FROM Table t2 WHERE t2.IsReady=1 AND t.ThicknessID=t2.ThicknessID ORDER BY DateTime DESC) 

Answer 2

 SELECT A.* FROM TABLE A, (SELECT ThicknessID,MAX(DateTime) AS MaxDateTime FROM TABLE WHERE IsReady=1 GROUP BY ThicknessID ) B WHERE A.ThicknessID=B.ThicknessID AND A.MaxDateTime = B.DateTime; 

Answer 3

Assuming you need separate lines for each SupplierID :

 select t.ID, t.SupplierID, t.ThicknessID, t.[DateTime], t.IsReady from MyTable t inner join ( select SupplierID, ThicknessID, max([DateTime]) as MaxDateTime from MyTable where IsReady = 1 group by SupplierID, ThicknessID ) a on t.ThicknessID = a.ThicknessID and t.SupplierID = a.SupplierID and t.MaxDateTime = a.[DateTime] 

Answer 4

The selected query will work fine. But this query will work much faster and use less resources.

 select distinct (SupplierID, ThicknessID) * from ( select * from Table1 where IsReady = 1 order by [DateTime] desc ) 

I tried this and it is much more productive