-1

I am using below query :

Select identifier 
from batch_instance 
where batch_class_id IN ( Select id 
                          from batch_class  
                          where ROWNUM <2 AND 
                          batch_class_name='abc' 
                          ORDER BY creation_date DESC);

PFB error :

ERROR at line 1: ORA-00907: missing right parenthesis

Jaydip Jadhav
  • 12,179
  • 6
  • 24
  • 40
Anshul
  • 199
  • 1
  • 3
  • 14
  • You can't have an `order by` in a subquery - just remove that. ([Possible duplicate](http://stackoverflow.com/q/21859866/266304), though not the clearest question; I'll keep looking...) – Alex Poole Oct 25 '16 at 14:05
  • Also where will be applied before order by so subquery is not returning newest id for 'abc' – Kacper Oct 25 '16 at 14:13
  • 1
    The construct is wrong anyway if you're trying to decide which two rows in batch_class are used. [This answer](http://stackoverflow.com/a/28451660/266304) might point you in the right direction. – Alex Poole Oct 25 '16 at 14:14
  • 1
    @AlexPoole - I looked at that other answer... it just occurs to me I could do some testing on this, but in my mind using `keep (dense_rank first/last)` should be preferred in such situations. Finding first/last should take at most O(n) time (O(log n) if `creation_date` is indexed), while ordering the whole set just to pick the first or last row is O(n log n) time. Right? –  Oct 25 '16 at 15:02
  • @mathguy - except Oracle uses stop count when it notices this sort of thing, so if it's indexed I wouldn't expect it to actually order everything. But yes, using `keep` is neater and clearer and may be more (or at least *as*) efficient - I don't think I've compared them properly in this situation either. You could maybe join and not use a subquery at all, with `max(identifier) keep...` but that would need further investigation too. – Alex Poole Oct 25 '16 at 15:07
  • @AlexPoople : Thanks removing order by from subquery solved issue.Just to add,above query shared by me works fine on mysql.Correct query for oracle : Select identifier from batch_instance where batch_class_id IN(Select id from batch_class where ROWNUM =1 AND batch_class_name='abc' )ORDER BY creation_date DESC; – Anshul Oct 26 '16 at 05:21

1 Answers1

1

The subquery (assuming it's fixed) will return one row only. So it returns only one id. In that case, there is no point in using an IN condition, an "equal" comparison will have the same effect.

To select the most recent id associated with the name 'abc', you can use keep (dense_rank last) so it's still all done in a single subquery - you won't need nested subqueries. Something like:

select identifier
from   batch_instance
where  batch_class_id = ( select max(id) keep (dense_rank last order by creation_date)
                          from   batch_class
                          where  batch_class_name =    'abc'
                        )
;