Selecting from a table of duplicate records 1

[MattDavies51]

I have the following records within a single table, Orders:

OrderNo Version Value
1 0 10.00
1 1 12.00
1 2 13.00
2 0 9.00
3 0 10.00
4 0 12.00
4 1 13.00
5 0 56.00
6 1 12.00
7 1 12.00
7 2 23.00

From this table, I have successfully extracted a list of orders with the minimum version only, eg. Order 1 version 0; order 7 version 1.

But I am having problems trying to extract all other orders(returning ALL versions greater than the minimum version) eg. Order 1 version 1 and 2; Order 4 version 1.

For orders with only 1 record(such as order 6), this has been selected in the first select statement so should be ignored for the 2nd statement.

 

[swampBoogie]

select * from orders as o
where version > (
    select min(version)
      from orders
     where OrderNo = o.OrderNo
   )

 

[MattDavies51]

Thank you very much SwampBoogie for your help.

Regards
Matt