-
[Resolved] Query using Max value
In the query below I need to be able to retrieve the max value for bm.revision_number within each bm.movement_number ie I just need one record for each bm.movement_number. Not sure how to use MAX. Thank you.
Code:
SELECT distinct(bm.movement_number), MAX(bm.revision_number) OVER (PARTITION BY bm.movement_number) Max_Revision_Number, bm.movement_date, bm.batch_voyage_number,
bm.reference_number, bm.type, bm.status, bm.bulk_movement_id, bp.terminal_product_number,
bp.terminal_product_number, bp.supplier_number, bp.tank_code, bp.gross_quantity,
bp.net_quantity, tp.product_group_code, tp.name, tp.petroex_product_code,
sp.petroex_company_code
FROM [VEMO-FACS1].FUELFACS.dbo.bulk_movement AS bm
INNER JOIN [VEMO-FACS1].FUELFACS.dbo.bulk_product AS bp ON bp.bulk_movement_id = bm.bulk_movement_id
INNER JOIN [VEMO-FACS1].FUELFACS.dbo.terminal_product AS tp ON tp.terminal_product_number = bp.terminal_product_number
INNER JOIN [VEMO-FACS1].FUELFACS.dbo.supplier AS sp ON sp.supplier_number = bp.supplier_number
group by bm.movement_number, bm.revision_number,
bm.movement_date, bm.batch_voyage_number,
bm.reference_number, bm.type, bm.status, bm.bulk_movement_id, bp.terminal_product_number,
bp.terminal_product_number, bp.supplier_number, bp.tank_code, bp.gross_quantity,
bp.net_quantity, tp.product_group_code, tp.name, tp.petroex_product_code,
sp.petroex_company_code
The result I get is (movement_number, revision_number)
1 4
1 4
1 4
1 4
1 4
The content of the table is
movement_number, revision_number
1 0
1 1
1 2
1 3
1 4
I this case I just need record with revision_number = 4
Last edited by snufse; 03-29-2011 at 01:05 PM.
-
snufse, I think this will get you what you want. May need some modification depending on your data.
Code:
SELECT bm.movement_number, bm.revision_number, bm.movement_date, bm.batch_voyage_number,
bm.reference_number, bm.type, bm.status, bm.bulk_movement_id, bp.terminal_product_number,
bp.terminal_product_number, bp.supplier_number, bp.tank_code, bp.gross_quantity,
bp.net_quantity, tp.product_group_code, tp.name, tp.petroex_product_code,
sp.petroex_company_code
FROM (
SELECT movement_number , revision_number, movement_date, batch_voyage_number,
reference_number, type , status , bulk_movement_id
FROM [VEMO-FACS1].FUELFACS.dbo.bulk_movement AS bm1
WHERE REVISION_NUMBER = (SELECT MAX(REVISION_NUMBER
FROM [VEMO-FACS1].FUELFACS.dbo.bulk_movement AS bm2
WHERE BM1.MOVEMENT_NUMBER = BM2.MOVEMENT_NUMBER
)
) AS BM
INNER JOIN
[VEMO-FACS1].FUELFACS.dbo.bulk_product AS bp
ON bp.bulk_movement_id = bm.bulk_movement_id
INNER JOIN
[VEMO-FACS1].FUELFACS.dbo.terminal_product AS tp
ON tp.terminal_product_number = bp.terminal_product_number
INNER JOIN
[VEMO-FACS1].FUELFACS.dbo.supplier AS sp
ON sp.supplier_number = bp.supplier_number
Basically, it is using a derived table to get the MAX Revision_number from the BM table before joining to the other tables.
-
Hello Sdas,
Nice of you to take the time to help me out.
Code:
FROM (
SELECT movement_number , revision_number, movement_date, batch_voyage_number,
reference_number, type , status , bulk_movement_id
FROM [VEMO-FACS1].FUELFACS.dbo.bulk_movement AS bm1
WHERE REVISION_NUMBER = (SELECT MAX(REVISION_NUMBER
FROM [VEMO-FACS1].FUELFACS.dbo.bulk_movement AS bm2
WHERE BM1.MOVEMENT_NUMBER = BM2.MOVEMENT_NUMBER
)
Getting an error:
Incorrect syntax near the keyword 'FROM'.
It points to line:
Code:
FROM [VEMO-FACS1].FUELFACS.dbo.bulk_movement AS bm2
-
Never mind, there was a ")" missing, found it and it is working great.
Thank you VERY much.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|