AMIT -

That got me what I needed - thank you so much!

Thank you but this does not work, it will only bring back the name_col with the max rev_no - 'ABC'.

I want all name_col's for the key_col ('XYZ') and their max...

Hope this helps - thank you...

BL_TRUCK_BOL_DEST_COMM_ORDER specifies how much of a commodity has been order by a specific order.  BL_TRUCK_BOL_DEST_COMM specifies the total amount of product the destination plans to...

TABLE_B has a column (order_no) which is not contained on TABLE_A. Or, maybe it's better for me to...

SELECT TABLE_A.col_a, TABLE_A.col_b, TABLE_A.col_c,

col_d = (SELECT col_d FROM _B ...)

FROM TABLE_A

Since I am using the 'computed' columns as just placeholders to make the UNION work, I always want to use the values from the 'REAL' columns. So I woulkd want...

col_c does not exist in TABLE_B - I'm just using it as a placeholder for the UNION. What can I do to get 1 row?

Here's an example of my data

bol_no.....dest_bol_no

00001.....2BT

00001....1BT

.............4BT

.............5BT

If I give it @bol = 00001 - I want back 2BT & 1BT

If I give it @bol = '',@dest_bol = '4BT' - I want...

If I do that:

SELECT *

FROM BL_TRUCK_BOL_DEST 

WHERE bol_no = ''

union

SELECT *

FROM BL_TRUCK_BOL_DEST 

WHERE bol_no = ''

  and dest_bol_no = '2BT'

union

SELECT *

FROM BL_TRUCK_BOL_DEST 

WHERE bol_no is NULL

  and dest_bol_no =...

GOVINN -

That worked great. I had to add a where clause though. Thank you very much - it really helped me out.

UPDATE A

SET

 max_rev_yorn = 'Y'

FROM

 BL_TRUCK_BOL_DEST A

JOIN

 (SELECT bol_no, dest_bol_no, MAX(rev_no)...

That doesn't work. Here's what it produces:

dest_bol_no...rev_no...max_rev_yorn

000002BT        32 .......Y

000003BT        32 .......Y

000004BT        8  ........

000005BT        10 ........

000006BT        32 ......Y

4BT & 5BT don't get updated.

Here's my update:

UPDATE BL_TRUCK_BOL_DEST

Set max_rev_yorn = 'Y' WHERE rev_no in

(select rev_no from BL_TRUCK_BOL_DEST A

WHERE bol_no = '000001'

AND rev_no = (select max(rev_no) from BL_TRUCK_BOL_DEST B

where A.dest_bol_no = B.dest_bol_no))

AND

dest_bol_no in...

Table 1 has some more values that I need in my result set that I didn't show.

Table 1

col_1.......col_2

1000......ABC

Table 2

col_1.....code.....value

1000......M........200

1000......R.........300

1000......X........350

I want

col_1.......col_2.....code.....value

1000......ABC........M..........200

1000......ABC........R..........300

But I want 1 row. Maybe something like....

col_1.......col_2.....code_1.....code_2....value_1.....value2

1000........ABC........M..........R.........200........300

Thank you all so much for your help. I have it working now due to your efforts!

Thanks Chris -

Sorry, it's always a little difficult to explain these properly.

In your post above, where you start "A number of questions"....Everything after that is correct in your assumptions....

What if gross days are 2 or gross days are 50?