This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Dec 18, 2012 10:35 PM by jeneesh RSS

top n query

947771 Newbie
Currently Being Moderated
Hi

how to find top 10 employees if all of them have unique salary.

1) SELECT *
FROM (SELECT * FROM employees ORDER BY sal desc)
WHERE ROWNUM < 11;

2) SELECT *
FROM employees
WHERE ROWNUM < 11
ORDER BY sal;


is the second wrong and first one is right?

yours sincerely

Edited by: 944768 on Dec 12, 2012 8:10 AM
  • 1. Re: top n query
    Paul Horth Expert
    Currently Being Moderated
    944768 wrote:
    Hi

    how to find top 5 employees if all of them have unique salary.

    1) SELECT *
    FROM (SELECT * FROM employees ORDER BY sal)
    WHERE ROWNUM < 11;

    2) SELECT *
    FROM employees
    WHERE ROWNUM < 11
    ORDER BY sal;


    is the second wrong and first one is right?

    yours sincerely
    Both are wrong.
  • 2. Re: top n query
    APC Oracle ACE
    Currently Being Moderated
    Clearly both queries are wrong if you want to return the top five employees, as rownum <11 will return ten rows.

    Rownum is applied before the ordering, so the second query won't return a result set featuring all the highest earners.

    Also, the default order is ascending, hence lowest first. If you want the top earners first you need to
    order by sal DESC
    So canonical correct solution would be:
    SELECT *
    FROM (SELECT * FROM employees ORDER BY sal DESC)
    WHERE ROWNUM < 6
    Cheers, APC
  • 3. Re: top n query
    Stew Ashton Expert
    Currently Being Moderated
    944768 wrote:
    how to find top 5 employees if all of them have unique salary.

    1) SELECT *
    FROM (SELECT * FROM employees ORDER BY sal)
    WHERE ROWNUM < 11;

    2) SELECT *
    FROM employees
    WHERE ROWNUM < 11
    ORDER BY sal;

    is the second wrong and first one is right?
    Why do you say "top 5" and then "11" in the code?

    To get the "top n" you have to sort first, then take the top n.

    The first statement sorts, then takes the first 10.

    The second statement takes the first 10 in whatever order the database wants, then orders them.

    The first statement is a "top 10" statement, the second statement is practically meaningless.

    Edited by: Stew Ashton on Dec 11, 2012 5:07 PM
  • 4. Re: top n query
    Paul Horth Expert
    Currently Being Moderated
    The first statement is a "top 10" statement, the second statement is practically meaningless.
    ...or bottom 10 :-)
  • 5. Re: top n query
    APC Oracle ACE
    Currently Being Moderated
    Paul  Horth wrote:
    >
    Both are wrong.
    Paul, that's correct but unhelpful. Why not be a better citizen?

    Cheers, APC

    Edited by: APC on Dec 13, 2012 2:41 PM
  • 6. Re: top n query
    John Spencer Oracle ACE
    Currently Being Moderated
    In addition to APC's comments about both queries being wrong, even if you had rownum < 6, which would be the correct predicate to get 5 rows back, the first query is still wrong because it would return the 5 lowest paid employees.

    John
  • 7. Re: top n query
    padders Pro
    Currently Being Moderated
    Why not be a better citizen?
    Oooh you've gone all dystopian.
  • 8. Re: top n query
    Paul Horth Expert
    Currently Being Moderated
    APC wrote:
    Paul  Horth wrote:
    >
    Both are wrong.
    Paul, that's correct but helpful. Why not be a better citizen?

    Cheers, APC
    Yes, it was helpful :-) Though I think you meant not helpful.

    Sorry, I'm just not a big believer in spoon-feeding. It's one of those questions that can be fairly easily googled
    and I wanted the O/P to put a bit of effort and thinking into it.

    It may be just me but I remember things a lot better if I've worked it out for myself rather than someone giving me
    the whole answer (though hints are welcome!)

    So, I was a bit terse (but did accurately answer the question he posted :-) )
  • 9. Re: top n query
    971895 Journeyer
    Currently Being Moderated
    Why don't you try like..if is giving as you excepted that is right else it is incorrect...
  • 10. Re: top n query
    APC Oracle ACE
    Currently Being Moderated
    Yes Paul you're right, I did mean "unhelpful", because your response was unhelpful. It was not "terse" it was a put-down, with no explanation. We like the OPs to provide lots of details regarding their problem: it is beholden on us to provide sufficient details in our responses.

    That doesn't mean we have to spoonfeed everyone who comes here with a question that they could have answered with a bit of informed Googling. But the overwhelming proportion of Forum newbies are also Oracle newbies. Most of them require more than a hint or a nudge to put them on the right track. So spoonfeeding is the order of the day.

    If you don't feel like giving a decent answer to a dull or dumb question then you have that right. We're all volunteers here. Just move on and find a better question. Otherwise the dark side will embrace you and you'll end up like Sybrand Bakker ;)

    tl:dr

    there is a difference between not spoon feeding and being snarky .

    Cheers, APC

    Edited by: APC on Dec 13, 2012 3:14 PM
  • 11. Re: top n query
    Paul Horth Expert
    Currently Being Moderated
    APC wrote:
    Yes Paul you're right, I did mean "unhelpful", because your response was unhelpful. It was not "terse" it was a put-down, with no explanation. We like the OPs to provide lots of details regarding their problem: it is beholden on us to provide sufficient details in our responses.

    That doesn't mean we have to spoonfeed everyone who comes here with a question that they could have answered with a bit of informed Googling. But the overwhelming proportion of Forum newbies are also Oracle newbies. Most of them require more than a hint or a nudge to put them on the right track. So spoonfeeding is the order of the day.

    If you don't feel like giving a decent answer to a dull or dumb question then you have that right. We're all volunteers here. Just move on and find a better question. Otherwise the dark side will embrace you and you'll end up like Sybrand Bakker ;)

    tl:dr

    there is a difference between not spoon feeding and being snarky .

    Cheers, APC

    Edited by: APC on Dec 13, 2012 3:14 PM
    You may have a point on some of the things you say, but I object to you saying it was a put-down.
    That attributes feelings to me that are just not true. You read my reasons and, in my way ,I was trying to be helpful by attempting to get the O/P to learn for themselves.
    In your opinion I was not helplful enough, but it was not meant as a put-down.
  • 12. Re: top n query
    887479 Newbie
    Currently Being Moderated
    APC wrote:
    If you don't feel like giving a decent answer to a dull or dumb question then you have that right. We're all volunteers here. Just move on and find a better question.
    But I wonder, why this logic is not applicable to you?Why cant you just ignore Paul's post and move on to next question...? O rules/logics are not equally applicable to all?
  • 13. Re: top n query
    APC Oracle ACE
    Currently Being Moderated
    884476 wrote:
    But I wonder, why this logic is not applicable to you?Why cant you just ignore Paul's post and move on to next question...? O rules/logics are not equally applicable to all?
    Touche! :) I could ignore your parry but I choose to respond instead.

    If someone gives an answer which is technically wrong do we let it stand or do we correct them? Obviously we point out the error and post a correct solution. But what if the answer is, um, spiritually wrong?

    I have been using the OTN Forums since 2000 (this is my second account here). I have strong feelings about the quality of the community. So while I usually ignore mean answers sometimes I suggest the reponder could have been more helpful, more positive. You obviously think that makes me a hypocrite. Perhaps you are right.

    This thread is not the appropriate place for such a discussion but alas it is a limitation of the Forum's architecture that there isn't a better place.

    Cheers, APC
  • 14. Re: top n query
    947771 Newbie
    Currently Being Moderated
    thank u,

    one more issue i have seen people having habbit of writting

    rownum <=1 to get top 1 , so please tel me which one is good and i should follow rownum<=1 or rownum < 2

    yours sincerely
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points