Possibly extremely simple SQL Query

Right, I'm no SQL programmer. As I type this, I have roughly the third the hair I had at 5 o'clock last night. I even lost sleep over it.

I'm trying to return a list of records from a database holding organisation names. As I've built a table to hold record versions, the key fields (with sample data) from a View I created to display this is as follows:

record_id--org_id--live--version
====== ===== === =====
1----1----0----1
2----2----0----1
3----1----1----2
4----2----0----2

as you can see the record id will always be unique. record 3 is a newer version of record 1, and 4 of 2. the issue is thus: i only want to return unique organisations. if a version of the organisation record is live on the system (in this case record id 3), i want to return the live version with its unique record id. i'm assuming for this i can perform a simple "SELECT WHERE live = 1" query.

however, some organisations will have no live versions (see org with id 2). i still wish to return a record for this organisation, but in this case the most recent version ie version 2 (and again - its unique record id)

in actual fact, it seems so much clearer when laid out like this. however, i feel it's not going to happen this end, and so any help would be greatly appreciated.

many thanks in advance,

philtry self joining subquery:

select a.*
from table_name a
where a.live = 1 or
(a.live = 0 and a.version = (select max(b.version) from table_name b where a.org_id = b.org_id ))|||spot on. told you it was simple. cheers!