Select w/ Max
-
I have a table similar to the following:
Table_A
truck_id.....trailer_id.....loaded_unloaded....weight.....scale_dt
123.............456..........U........................40000......1-1-2007 10:00
123.............456..........U........................45000......1-1-2007 11:00
123.............456..........U........................41000......1-1-2007 12:00
123.............456..........L........................50000......1-1-2007 10:00
123.............456..........L........................55000......1-1-2007 11:00
123.............456..........L........................51000......1-1-2007 12:00
I want to select the LAST 'U' record and the last 'L' record. If a 'U' record doesn't exist then I want the last 'L' record and vice versa. So, my results would look like this:
123.............456..........U........................41000......1-1-2007 12:00
123.............456..........L........................51000......1-1-2007 12:00
-
select a.*
from table_a a
join
(
select
loaded_unloaded,
last_dt = max(scale_dt)
from table_a
group by loaded_unloaded
)t
on a.loaded_unloaded = t.loaded_unloaded
and a.scale_dt = t.last_dt
-
To keep it simpler...
-- Prepare sample data
DECLARE
@Sample TABLE (Truck_ID INT, Trailer_ID INT, Loaded_Unloaded VARCHAR(1), Weight INT, Scale_DT DATETIME)
INSERT
@Sample
SELECT 123, 456, 'U', 40000, '1-1-2007 10:00' UNION ALL
SELECT 123, 456, 'U', 45000, '1-1-2007 11:00' UNION ALL
SELECT 123, 456, 'U', 41000, '1-1-2007 12:00' UNION ALL
SELECT 123, 456, 'L', 50000, '1-1-2007 10:00' UNION ALL
SELECT 123, 456, 'L', 55000, '1-1-2007 11:00' UNION ALL
SELECT 123, 456, 'L', 51000, '1-1-2007 12:00'
-- Show the expected output
SELECT TOP 1 WITH TIES
Truck_ID,
Trailer_ID,
Loaded_Unloaded,
Weight,
Scale_DT
FROM @Sample
ORDER BY Scale_DT DESC
N 56°04'39.16"
E 12°55'05.25" -
Peter - your solution assumes times for records U and L are same...which may not be true..at least it's not mentioned in the requirement here.
Solution from Mark works well.
-
Yes. But is it the right one? Mine also works according to original posting.
It's a matter of interpretation. Until original poster comes back, all we can do is guessing.
N 56°04'39.16"
E 12°55'05.25" -
And since this is a SQL Server 2005 forum, why not post a better performing query, than Mark's?
DECLARE
@Sample TABLE (Truck_ID INT, Trailer_ID INT, Loaded_Unloaded VARCHAR(1), Weight INT, Scale_DT DATETIME)
INSERT
@Sample
SELECT 123, 456, 'U', 40000, '1-1-2007 10:00' UNION ALL
SELECT 123, 456, 'U', 45000, '1-1-2007 11:00' UNION ALL
SELECT 123, 456, 'U', 41000, '1-1-2007 12:00' UNION ALL
SELECT 123, 456, 'L', 50000, '1-1-2007 10:00' UNION ALL
SELECT 123, 456, 'L', 55000, '1-1-2007 11:00' UNION ALL
SELECT 123, 456, 'L', 51000, '1-1-2007 12:00'
-- Show the expected output
SELECT Truck_ID,
Trailer_ID,
Loaded_Unloaded,
Weight,
Scale_DT
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY Truck_ID, Trailer_ID, Loaded_Unloaded ORDER BY Scale_DT DESC) AS RecID
FROM @Sample
) AS x
WHERE RecID = 1
I'd like to see your suggestion.
N 56°04'39.16"
E 12°55'05.25" -
WOW! So glad to read that post!
ROW_NUMBER() is going eliminate so many derived tables for getting a max/min value and then joining to the original query.
Example SQL Below
UPDATE #assembly
SET #assembly.assemblyStatusID = maxStat.assemblyStatusID
, #assembly.assemblyStatus = maxStat.assemblyStatus
, #assembly.assemblyStatusDate = maxStat.historyDateStamp
FROM
(
SELECT bar.projectID, bar.asi_instanceID, s2s.assemblyStatusID, s2s.assemblyStatus, hist.historyDateStamp,
ROW_NUMBER() over ( partition by bar.projectID, bar.asi_instanceID ORDER BY s2s.assemblyStatusID desc) as recID
FROM #assembly asm
INNER JOIN barcodeBarcode bar ON asm.projectID = bar.projectID
AND asm.asi_instanceID = bar.asi_instanceID
INNER JOIN barcodeHistory hist ON bar.barcodeMark = hist.barcodeMark
INNER JOIN @Scan2Status s2s ON hist.opID = s2s.opid
) maxStat
WHERE maxStat.recid = 1 AND #assembly.projectID = maxStat.projectID AND #assembly.asi_instanceID = maxStat.asi_instanceID
thanks
daralick
Note: #assembly gets lots of data, reason for temp and variable table mix.
-
Would not be easy to cheating union operator?
select * from (select * from Table_A where loaded_unloaded ='U' order by scale_dt desc)A
UNION
select * from (select * from Table_A where loaded_unloaded ='L' order by scale_dt desc)B
-
You only cheat yourself.
You will get a resultset of all records.
N 56°04'39.16"
E 12°55'05.25" -
You could at least have some decency to comment if our previous suggestions are to your satisfaction...
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=338&messageid=355182
N 56°04'39.16"
E 12°55'05.25" -
For Peter:
Sorry about being indecent. The scope of my request changed as you saw in my other post and which btw - I was very appreciative. Thanks for your suggestions.
-
Peter's select will not work if I add the following in GREEN
INSERT @Sample
SELECT 123, 456, 'U', 40000, '1-1-2007 10:00' UNION ALL
SELECT 123, 456, 'U', 45000, '1-1-2007 11:00' UNION ALL
SELECT 123, 456, 'U', 41000, '1-1-2007 12:00' UNION ALL
SELECT 123, 456, 'L', 50000, '1-1-2007 10:00' UNION ALL
SELECT 123, 456, 'L', 55000, '1-1-2007 11:00' UNION ALL
SELECT 123, 456, 'L', 51000, '1-1-2007 12:00' UNION ALL
SELECT 333, 888, 'L', 77000, '1-3-2007 13:00'
This would only brong back one record - the '333' record.
-
Which code? The first suggestion or the second suggestion?
N 56°04'39.16"
E 12°55'05.25" -
When I added the '333' record from above and ran your suggestion:
SELECT TOP 1 WITH TIES......
it only brought back the one record ('333') which had the latest date. I would expect 3 records.
I also tried the row_number sql you suggested but I'm afraid I don't quite understand it. When I try and run it, my compiler doesn't know what row_number means. When, in query analyzer, I look in help for row_number, I get no matches. I'm sure I'm overlooking something simple.
Thank you
-
ROW_NUMBER() is a SQL Server 2005 function.
I suggested that appraoch because this is a SQL Server 2005 forum.
N 56°04'39.16"
E 12°55'05.25"
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply