June 3, 2008 at 8:07 am
I have a scales table. Below is an example of data which may be in the table. A truck comes in with a trailer (or 2 trailers). I want to select the last records based on bol_no. SInce the below records indicate I have 2 trailers for bol 123 (trailer1, trailer2), my result set should be the records in at the bottom (desired results) because those were the last weights for each trailer for that bol.
truck_id...trailer_id...load_date...........trailer_wt...bol_no.....le_ind
truckA.....trailer1.....07-07-03 13:08...2000.0000....123........E
truckA.....trailer1.....07-07-03 14:09...2200.0000....123........E
truckA.....trailer2.....07-07-03 13:08...1000.0000....123........E
truckA.....trailer2.....07-07-03 13:09...1100.0000....123........E
desired results:
truckA.....trailer1.....07-07-03 14:09...2200.0000....123........E
truckA.....trailer2.....07-07-03 13:09...1100.0000....123........E
June 3, 2008 at 8:40 am
Rog Saber (6/3/2008)
I have a scales table. Below is an example of data which may be in the table. A truck comes in with a trailer (or 2 trailers). I want to select the last records based on bol_no. SInce the below records indicate I have 2 trailers for bol 123 (trailer1, trailer2), my result set should be the records in at the bottom (desired results) because those were the last weights for each trailer for that bol.truck_id...trailer_id...load_date...........trailer_wt...bol_no.....le_ind
truckA.....trailer1.....07-07-03 13:08...2000.0000....123........E
truckA.....trailer1.....07-07-03 14:09...2200.0000....123........E
truckA.....trailer2.....07-07-03 13:08...1000.0000....123........E
truckA.....trailer2.....07-07-03 13:09...1100.0000....123........E
desired results:
truckA.....trailer1.....07-07-03 14:09...2200.0000....123........E
truckA.....trailer2.....07-07-03 13:09...1100.0000....123........E
Something like this maybe:
SELECT y.truck_id
,y.trailer_id
,y..load_date
,x.trailer_wt
,y.bol_no
,x.le_end
FROM (SELECT truck_id
,trailer_id
MAX(load_date) as load_date
,bol_no
FROM TableX
WHERE bol_no = 123
GROUP BY truck_id,trailer_id,bol_no) y
JOIN TableX x
ON y.truck_id = x.truck_id
and y.trailer_id = x.trailer_id
and y.bol_no = x.bol_no
and y.load_date = x.load_date
Untested.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 3, 2008 at 8:43 am
Rog Saber (6/3/2008)
I have a scales table. Below is an example of data which may be in the table. A truck comes in with a trailer (or 2 trailers). I want to select the last records based on bol_no. SInce the below records indicate I have 2 trailers for bol 123 (trailer1, trailer2), my result set should be the records in at the bottom (desired results) because those were the last weights for each trailer for that bol.truck_id...trailer_id...load_date...........trailer_wt...bol_no.....le_ind
truckA.....trailer1.....07-07-03 13:08...2000.0000....123........E
truckA.....trailer1.....07-07-03 14:09...2200.0000....123........E
truckA.....trailer2.....07-07-03 13:08...1000.0000....123........E
truckA.....trailer2.....07-07-03 13:09...1100.0000....123........E
desired results:
truckA.....trailer1.....07-07-03 14:09...2200.0000....123........E
truckA.....trailer2.....07-07-03 13:09...1100.0000....123........E
WITH cte
AS
( SELECT truck_id,trailer_id,load_date,trailer_wt,bol_no,le_ind,
ROW_NUMBER() OVER(PARTITION BY trailer_id, truck_id ORDER BY load_date DESC) rn
)
SELECT truck_id,trailer_id,load_date,trailer_wt,bol_no,le_ind
FROM cte
WHERE rn = 1
hth
Note: some happyface was comming up unexpectedly
* Noel
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply