1 2 Previous Next 15 Replies Latest reply: Dec 19, 2012 12:35 AM by jeneesh RSS

    top n query

    947771
      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
          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
            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
              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
                The first statement is a "top 10" statement, the second statement is practically meaningless.
                ...or bottom 10 :-)
                • 5. Re: top n query
                  APC
                  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
                    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
                      Why not be a better citizen?
                      Oooh you've gone all dystopian.
                      • 8. Re: top n query
                        Paul  Horth
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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