Results 1 to 5 of 5

Thread: Question

  1. #1
    Join Date
    Aug 2011
    Posts
    2

    Unanswered: Question

    select pub_name from publishers where pub_id in (select pub_id from titles1
    group by pub_id having count(title_id)>0 order by count(title_id) desc) and rownum <4 ;

    this query doesnt work when order by is used in the inner query. Please expalin????

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by nishithc View Post
    this query doesnt work when order by is used in the inner query. Please expalin????
    You are very unlucky person to face the "doesnt work" behaviour of Oracle. I never faced it so I cannot suggest anything to get rid of it.

    The only thing comes to my mind though - the ORDER BY has no effect in IN clause. Just think about it - there is no difference between these two conditions:
    Code:
    PUB_ID in ( 1, 2, 3, 4, 5, 6, 7, 8 )
    PUB_ID in ( 1, 3, 5, 7, 2, 4, 6, 8 )
    Without knowing what you want to achieve it is hard to build the correct query. Maybe join with the aggregates or Oracle analytic ROW_NUMBER function could return the expected result without "doesnt work". You may try using one of them.

  3. #3
    Join Date
    Aug 2011
    Posts
    2
    I know that an order by would not change the final result any how but i want to know why is this not working what the reason behind this and yes i am able to get the result if i omit this 'doesnt work' condition but knowing the concept behind this is the reason i have posted this query.

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    Maybe you could get more meaningful answer(s) if you exactly specified what "not working" exactly means.

    Without it, I may only repeat myself - the subquery with or without ORDER BY returns the same PUB_ID values. The whole query itself returns at most three random rows from PUBLISHERS satisfying the WHERE condition. So, they may be different for the different subquery, or even when running twice the same query. They may be the same. No guarantee, as the query is not deterministic.

  5. #5
    Join Date
    Aug 2011
    Posts
    1

    Doubt

    Can Anyone tell me about what is Oracle Apps Techno-Functional?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •