How to sort rows with SQL: Databases for Developers #12

To guarantee your queries return rows in a particular sequence, you must use an order by!
This episode shows you how to do this and limit your results to the first N rows in an ordered set.
Want to know more? Take the course: devgym.oracle.com/pls/apex/dg...
Order by clause documentation: docs.oracle.com/en/database/o...
Need help with SQL? Ask us over on AskTOM: asktom.oracle.com
Twitter: / chrisrsaxon
Daily SQL Twitter tips: / sqldaily
All Things SQL blog: blogs.oracle.com/sql/
Videos produced by Philip Lima productions.
============================
The Magic of SQL with Chris Saxon
Copyright © 2015 Oracle and/or its affiliates. Oracle is a registered trademark of Oracle and/or its affiliates. All rights reserved. Other names may be registered trademarks of their respective owners. Oracle disclaims any warranties or representations as to the accuracy or completeness of this recording, demonstration, and/or written materials (the “Materials”). The Materials are provided “as is” without any warranty of any kind, either express or implied, including without limitation warranties or merchantability, fitness for a particular purpose, and non-infringement.

[@strielok1158]

What about clustered indexes? If a table has such an index, select from that table will always return the records in the same order (of course if there is no order by clause), is that correct? As far as I know, in many RDBMS, the primary key index is going to be clustered by default.

[@TheMagicofSQL]

The only way to GUARANTEE a query returns rows in particular order is by adding an ORDER BY to it.
Even with a clustered table (only the default in SOME databases), the query may return rows in a different order. A common reason is because it uses a different index to access the data.

[@user-vz1sb5zi8k]

Hi bro , I am having a top n query , taking long time ... the reason I found is the columns I am using in order by having no index. Could you help me?

[@TheMagicofSQL]

Sorting large tables can take a long time. Creating an index including columns in the where clause and sort can help. Filtering the data before the top-N so you process fewer rows should help too (if possible).
If you're using Oracle Database and need more help, post details to asktom.oracle.com and we'll see if we can assist. Ensure you include your query and its execution plan.

[@juno7893]

Perfect

[@TheMagicofSQL]

Thanks! :)

[@grigorvardanyan]

Really appreciate for the videos, but I believe they could've been a bit shorter

[@TheMagicofSQL]

What would you cut?

[@Ashishkumar-qo4jn]

Do not understand

[@TheMagicofSQL]

What don't you understand? Please clarify!