CODEWITHSUNDEEP
oracle-databasesubquery
FrankSharp
FrankSharp

Reputation: 2632

The first row when single-row subquery returns more than one row

My subquery returns more than one row and I need the first row and keep my max function 

Select ...
Where GHTY_FIRME.FIRME_ID= (Select   FIRME_ID
                           FROM imag_verification_tube 
                           WHERE (numero_dossier =  '12004' OR NUMERO_TIRE= '12004')
                           AND CREE_LE = (select max (CREE_LE) 
                           from tableX where(numero_dossier ='12004' OR 
                           NUMERO_PQDCS= '12004 ')));

Upvotes: 1

Views: 3121

Answers (1)

Nivas
Nivas

Reputation: 18364

Using the rownum pseudocolumn:

Select ...
Where GHTY_FIRME.FIRME_ID= (Select   FIRME_ID
                           FROM imag_verification_tube 
                           WHERE (numero_dossier =  '12004' OR NUMERO_TIRE= '12004')
                           AND CREE_LE = (select max (CREE_LE) 
                                          from tableX 
                                          where(numero_dossier ='12004' 
                                               OR NUMERO_PQDCS= '12004 ') 
                                               and rownum < 2));

See also On ROWNUM and Limiting Results.

That said, you can try to optimize the sql. One of the first things I would do is to convert the inner SQLs to joins. Also, depending on your business logic, the or condition could also be elminated.

Upvotes: 1

Related Questions