T-SQL: SELECT related column data for the max two

问题:

I have table data like the following, where order type is 1 for a quote, and 2 for an order. Any given po_num can have 0 to many of order_type 1, but should only have only 0 or 1 of order_type 2, or all of the above.

I need to return the max order_num of the max order_type of a given po_num, where the order_num is just an additional (but important) column in my result.

Table data:

order_type  po_num  order_num
1           E0102   1013200
1           E0102   1013162
1           E0104   1012161 
2           E0104   1012150
1           E0104   1011449
2           E0107   1010034
2           E0108   1011994

Desired result:

order_type  po_num  order_num
1           E0102   1013200
2           E0104   1012950
2           E0107   1010034
2           E0108   1011994

The closest I can get is this, which still includes the max(order_no) for both order_type of 1, and order_no of order type 2:

order_type  po_num  order_num
1           E0102   1013162
1           E0104   1012161
2           E0104   1012150
2           E0107   1010034
2           E0108   1011994

回答1:

I think you want this:

select order_type
     , po_num
     , max(order_num)
  from orders o1
 where order_type = (
         select max(order_type)
           from orders o2
          WHERE o2.po_num = o1.po_num
      ) 
 group by po_num,order_type

The inclusion of order_type in the group by is an artifact, it is required because of how the table is designed.

FWIW, the quotes and orders should be split out into two tables. When you get weird SQL like this an difficult or conditional unique constraints it is a table design issue.

回答2:

I assume you are using a group by clause. Could you add a

having order_type = max(order_type)

to your sql?

See http://msdn.microsoft.com/en-us/library/ms180199.aspx for more details on the 'having' statement.