I have a table of data (the db is MSSQL):
ID OrderNO PartCode Quantity DateEntered
417 2144 44917 100 18-08-11
418 7235 11762 5 18-08-11
419 9999 60657 100 18-08-11
420 9999 60657 90 19-08-11
I would like to make a query that returns OrderNO, PartCode and Quantity, but only for the last registered order.
From the example table I would like to get back the following info:
OrderNO PartCode Quantity
2144 44917 100
7235 11762 5
9999 60657 90
Notice that only one line was returned for order 9999.
Thanks!
This question is related to
sql-server
The best way is Mikael Eriksson, if ROW_NUMBER()
is available to you.
The next best is to join on a query, as per Cularis' answer.
Alternatively, the most simple and straight forward way is a correlated-sub-query in the WHERE clause.
SELECT
*
FROM
yourTable AS [data]
WHERE
DateEntered = (SELECT MAX(DateEntered) FROM yourTable WHERE orderNo = [data].orderNo)
Or...
WHERE
ID = (SELECT TOP 1 ID FROM yourTable WHERE orderNo = [data].orderNo ORDER BY DateEntered DESC)
SELECT t1.OrderNo, t1.PartCode, t1.Quantity
FROM table AS t1
INNER JOIN (SELECT OrderNo, MAX(DateEntered) AS MaxDate
FROM table
GROUP BY OrderNo) AS t2
ON (t1.OrderNo = t2.OrderNo AND t1.DateEntered = t2.MaxDate)
The inner query selects all OrderNo
with their maximum date. To get the other columns of the table, you can join them on OrderNo
and the MaxDate
.
select OrderNo,PartCode,Quantity
from dbo.Test t1
WHERE EXISTS(SELECT 1
FROM dbo.Test t2
WHERE t2.OrderNo = t1.OrderNo
AND t2.PartCode = t1.PartCode
GROUP BY t2.OrderNo,
t2.PartCode
HAVING t1.DateEntered = MAX(t2.DateEntered))
This is the fastest of all the queries supplied above. The query cost came in at 0.0070668.
The preferred answer above, by Mikael Eriksson, has a query cost of 0.0146625
You may not care about the performance for such a small sample, but in large queries, it all adds up.
This works for me. use MAX(CONVERT(date, ReportDate)) to make sure you have date value
select max( CONVERT(date, ReportDate)) FROM [TraxHistory]
This worked for me perfectly fine.
select name, orderno from (
select name, orderno, row_number() over(partition by
orderno order by created_date desc) as rn from orders
) O where rn =1;
And u can also use that select statement as left join query... Example :
... left join (select OrderNO,
PartCode,
Quantity from (select OrderNO,
PartCode,
Quantity,
row_number() over(partition by OrderNO order by DateEntered desc) as rn
from YourTable) as T where rn = 1 ) RESULT on ....
Hope this help someone that search for this :)
Try to avoid IN use JOIN
SELECT SQL_CALC_FOUND_ROWS * FROM (SELECT msisdn, callid, Change_color, play_file_name, date_played FROM insert_log
WHERE play_file_name NOT IN('Prompt1','Conclusion_Prompt_1','silent')
ORDER BY callid ASC) t1 JOIN (SELECT MAX(date_played) AS date_played FROM insert_log GROUP BY callid) t2 ON t1.date_played=t2.date_played
If you have indexed ID and OrderNo You can use IN: (I hate trading simplicity for obscurity, just to save some cycles):
select * from myTab where ID in(select max(ID) from myTab group by OrderNo);
rownumber() over(...) is working but I didn't like this solution for 2 reasons. - This function is not available when you using older version of SQL like SQL2000 - Dependency on function and is not really readable.
Another solution is:
SELECT tmpall.[OrderNO] ,
tmpall.[PartCode] ,
tmpall.[Quantity] ,
FROM (SELECT [OrderNO],
[PartCode],
[Quantity],
[DateEntered]
FROM you_table) AS tmpall
INNER JOIN (SELECT [OrderNO],
Max([DateEntered]) AS _max_date
FROM your_table
GROUP BY OrderNO ) AS tmplast
ON tmpall.[OrderNO] = tmplast.[OrderNO]
AND tmpall.[DateEntered] = tmplast._max_date
For MySql you can do something like the following:
select OrderNO, PartCode, Quantity from table a
join (select ID, MAX(DateEntered) from table group by OrderNO) b on a.ID = b.ID
Source: Stackoverflow.com