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

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.