SQL Selecting less significant entity from table.

Question

Hi, I have a problem with some query from given result set i need to select the less detail row from table under some conditions.

I have three selects that after union return this table

SELECT A_ID, B_ID, 1 FROM MY_TABLE JOIN MY_TABLE2 ON SPECIFIC CONDITION FOR LEVEL 1
UNION
SELECT A_ID, B_ID, 2 FROM MY_TABLE JOIN MY_TABLE2 ON SPECIFIC CONDITION FOR LEVEL 2
UNION
SELECT A_ID, B_ID, 3 FROM MY_TABLE JOIN MY_TABLE2 ON SPECIFIC CONDITION FOR LEVEL 3

The result can be something like this

1000 100 1
1000 200 2
1000 300 3

From this table the final result should be 1000 100 1

The best case scenario is when a value is found then is no longer searched in next select.

Some ideas ?

EDIT:

The solution presented by 'Jeffrey Kemp' one query works fine.

1000 100 1
1000 200 2
1000 300 3
1001 200 2
1001 300 3

result

   1000 100 1 
   1001 200 2

Database: Oracle Database 10g Release 10.2.0.4.0 - 64bit Production

Answer 1

Without knowing the details of your query, this is one option to consider:

SELECT * FROM (
   SELECT * FROM (
      SELECT A_ID, B_ID, 1 FROM MY_TABLE JOIN MY_TABLE2 ON SPECIFIC CONDITION FOR LEVEL 1
      UNION
      SELECT A_ID, B_ID, 2 FROM MY_TABLE JOIN MY_TABLE2 ON SPECIFIC CONDITION FOR LEVEL 2
      UNION
      SELECT A_ID, B_ID, 3 FROM MY_TABLE JOIN MY_TABLE2 ON SPECIFIC CONDITION FOR LEVEL 3
   )
   ORDER BY 3
) WHERE ROWNUM = 1;

An alternative is to add conditions to the queries to determine if they need to run at all:

SELECT A_ID, B_ID, 1 FROM MY_TABLE JOIN MY_TABLE2 ON SPECIFIC CONDITION FOR LEVEL 1
UNION
SELECT A_ID, B_ID, 2 FROM MY_TABLE JOIN MY_TABLE2 ON SPECIFIC CONDITION FOR LEVEL 2
WHERE NOT EXISTS (SPECIFIC CONDITION FOR LEVEL 1)
UNION
SELECT A_ID, B_ID, 3 FROM MY_TABLE JOIN MY_TABLE2 ON SPECIFIC CONDITION FOR LEVEL 3
WHERE NOT EXISTS (SPECIFIC CONDITION FOR LEVEL 1)
AND   NOT EXISTS (SPECIFIC CONDITION FOR LEVEL 2)

Of course, I don't know the nature of your "specific conditions" so I don't know if this will work for you or not.