asked on

DB2 ORDER BY QUESTION

DB2 10.5 on Windows Server 2012 R2

This question came up in a conversation and I want to make sure my answer is correct. If you have this query...

SELECT 
     ORDER_ID, 
     ORDER_ZIP, 
     ORDER_ADDRESS, 
     ORDER_LAST_NAME, 
     ORDER_NAME,
     ORDER_HASH,
     CREATED_TS
FROM
     ORDERS 
WHERE 
     CREATED_TS >= CURRENT TIMESTAMP - 6 MONTHS
     AND ORDER_HASH IS NULL 
ORDER BY 
     CREATED_TS DESC
FETCH FIRST 1000 ROWS only

Open in new window

Will DB2 get all the orders for last 6 months, sort them by CREATED_TS, and then return the first 1000 rows or will it grab the first 1000 rows for last 6 months, order them by CREATED_TS and return them?

I would think it has to grab all the orders for last 6 months, sort them and then return the data, otherwise it is not a true sort by CREATED_TS.

Is that correct?

Thank you!

Jim

ASKER CERTIFIED SOLUTION

Bill Prew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jim Youmans

ASKER

Thank you!

Kent Olsen

Hi Jim,

You're in luck. :) DB2 will do what you want, but efficiency may be an issue. Your query will return the 1,000 lowest dates in the table.

For some queries, DB2 will skip the intermediate sort and keep just the desired rows in a temp table by sorting "on the fly". I don't recall the rules for that. (I'll go look them up.)

But either way you'll get the lowest dates in the table, not a sort of random dates.

Kent