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