Tag: sorting

Add ORDER BY to make ANY query faster

Yes it’s SCBT day here in Perth! SCBT = Silly Click Bait Title This post is just a cautionary tale that it is easy to get caught up judging SQL performance solely on a few metrics rather than taking a more common sense approach…

ROWNUM and ORDER BY

Just a quick revisit on an old topic so I could link to this post in a Stack Overflow discussion. SQL> create table t ( x int ); Table created. SQL> SQL> insert into t 2 select dbms_random.value(1,1000) 3 from dual 4 connect by…

Never rely on an assumed order

We’ve (hopefully) all had it drummed into us enough times – you cannot assume the order of results from queries unless you explicitly include an ORDER BY statement. Here’s another trivial example of this- I was doing a little demo script for an AskTom…

A little known ORDER BY extension

Within a CONNECT BY statement, you can order siblings, that is, under a particular branch of the hierarchy, the child entries can be ordered, but you still preserve the hierarchy. SQL> select lpad(‘*’, level, ‘*’ ) || ename ename 2 from emp 3 start…

Mr DISTINCT might not be your friend

Whenever you have the need to use the DISTINCT keyword, its worth just pausing for a second, and making sure that you are not hiding just a larger issue. It actually might represent either incorrect use of SQL or incorrect assumptions from the data…