Results 1 to 4 of 4

Thread: Query using Max value

  1. #1
    Join Date
    Mar 2009
    Posts
    32

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

  2. #2
    Join Date
    Apr 2009
    Posts
    86
    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.

  3. #3
    Join Date
    Mar 2009
    Posts
    32
    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

  4. #4
    Join Date
    Mar 2009
    Posts
    32

    Smile

    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
  •