[Resolved] Retrieving MAX value by ...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

[Resolved] Retrieving MAX value by ...

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2011-03-29 : 12:55:22

In the query below I need to be able to retrieve the max value for bm.revision_number within each bm.movement_number. Not sure how to use MAX. Thank you.

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       [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

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-29 : 12:59:42

[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,
MAX(bm.revision_number) OVER (PARTITION BY bm.movement_number) Max_Revision_Number
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[/code]

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2011-03-29 : 13:31:20

Running the code:

SELECT	bm.movement_number, bm.revision_number, 
		MAX(bm.revision_number) OVER (PARTITION BY bm.movement_number) Max_Revision_Number
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
ORDER BY bm.movement_number, bm.revision_number

The result is:

1 0 4
1 1 4
1 2 4
1 3 4
1 4 4
2 0 2
2 1 2
2 2 2

I need just one record for each movement_number.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-29 : 13:32:17

SELECT bm.movement_number,
MAX(bm.revision_number) Max_Revision_Number
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
ORDER BY bm.movement_number

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2011-03-29 : 14:36:18

Great. Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources