CODEWITHSUNDEEP
sqloracle-database
BnJ
BnJ

Reputation: 1044

Order by in a subquery

Here is my query :

select count(1) into isMyCarExists 
    from CAR
    where ID_MODEL = (select ID 
                        from MODEL
                        where TYPE = 'SUV'
                        and rownum <= 1
                        order by DATE_UPDATE desc);

I want to count how many rows in CAR have the latest version (DATE_UPDATE desc) of the MODEL 'SUV'.

With this query, I got ORA-00907: missing right parenthesis...

I'm using Oracle Database 10.

Upvotes: 0

Views: 56

Answers (2)

Lalit Kumar B
Lalit Kumar B

Reputation: 49122

Forget about that error first. Your query is wrong functionally. Order by and rownum don't go together.

Since you are not on 12c, you need to have the order by first and then use where rownum = 1 in the outer query. Else, in 12c, it is quite easy using TOP-N query as fetch first 1 rows only

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271161

Even if you fixed the query, it wouldn't do what you want. The where is executed before the order by.

If the ids are assigned in increasing order, then this should work:

select count(*) as isMyCarExists
from car c
where id_model = (select max(id)
                  from model
                  where type = 'SUV'
                 );

Alternatively, use analytic functions:

select count(*) as isMyCarExists
from car c
where id_model = (select m.id
                  from (select m.*, row_number() over (order by date_update desc) as seqnum
                        from model m
                        where m.type = 'SUV'
                       ) m
                  where seqnum = 1
                 );

Upvotes: 3

Related Questions