select the latest date
Hi all, I have a problem selecting only the latest date of a record. explanation : table order = "order number" is unique table orders details (which is all the line in the order) = "confirm date" has for exemple 3 differents date I want the latest date in orders details for the order. order table: order detail table : OrderID|OrderNb|Custname|OrderType|SalesRep OrderID|ConfDate 1 |690001|Test01 |XXXX|YYYY| 1 |08/09/2011 1 |690001|Test01 |XXXX|YYYY| 1 |31/12/2011 1 |690001|test01 |XXXX|YYYY| 1 |NULL etc... I want one line with all information in orders table with the latest date (31/12/2011) 1|690001|Test01|XXXX|YYYY|31/12/2011 thank you for you help
September 11th, 2011 4:32am

Hi niarky, Use a in line Query like this. Select o.*,(select top 1 (od.ConfDate) from order_detail od where o.OrderID=od.OrderID order by od.ConfDate Desc) [Dates] from order o Rakesh M J Dont forget to mark it as Answered if found useful MCTS,MCITP,MCSS http://myspeakonbi.blogspot.com/
Free Windows Admin Tool Kit Click here and download it now
September 11th, 2011 4:53am

here is the query : SELECT new_ordernb, (SELECT TOP (1) new_confdate FROM dbo.FilteredNew_detailpositioncommande AS od WHERE (o.FilteredNew_commande.new_commandeid = od.FilteredNew_detailpositioncommande.new_commandeid) ORDER BY new_confdate DESC) AS Dates FROM dbo.FilteredNew_commande AS o I have an error : "invalid column 'FilteredNew_commande'" "invalid column 'FilteredNew_detailpositioncommande'"... all name were checked and are good
September 11th, 2011 5:43am

Try this one. SELECT new_ordernb, (SELECT TOP (1) new_confdate FROM dbo.FilteredNew_detailpositioncommande AS od WHERE (o.new_commandeid = od.new_commandeid) ORDER BY new_confdate DESC) AS Dates FROM dbo.FilteredNew_commande AS o Pls mark as answer, if this helps. - Kerobin
Free Windows Admin Tool Kit Click here and download it now
September 11th, 2011 5:54am

Thank you both of you ! You've saved my day !
September 11th, 2011 6:12am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics