Should ORDER BY change the result set?

Question

I was under the impression that using an ORDER BY in an SQL query would not affect which records were selected for the result set. I thought that ORDER BY would only affect the presentation of the result set.

Recently, however, I was getting unexpected results from a query until I used an ORDER BY clause. This suggests that either a) ORDER BY can affect which records are included in the result set, or b) I have some other bug which I need to work on.

Which is it?

Here's the query: SELECT node_id FROM users ORDER BY node_id LIMIT 100

(node_id is both a primary key and foreign key).

As you can see, the query includes a LIMIT clause. It seems that if I use the ORDER BY, the records are ordered before the top 100 are selected. I had expected it to select 100 records based on natural order, then order them according to node_id.

I've looked for info on ORDER BY but as yet, the only info I can find suggests that it affects presentation only... I am using MySQL.

Are you confident that what you mean by 'natural order' corresponds to MySQL's interpretation of the term? — Strawberry, Feb 01 '13 at 01:04

score 2 · Accepted Answer · answered Jan 31 '13 at 23:04

2

ORDER BY reflects the order of all of the records before the LIMIT Clause. To get the result you want you will need this:

select u.node_id
from users u
join
(
    SELECT node_id
    FROM users 
    LIMIT 100
) us ON u.node_id = us.node_id
ORDER BY u.node_id

This way you will use the limit clause first and get the top 100 records and then you will sort the result of that. The join clause is faster than a double Select statement especially if you are working with many records.

answered Jan 31 '13 at 23:04

nathan hayfield

2,627
1
17
28

Hi Natahn, your first sentence, ORDER BY reflects the order of all of the records before the LIMIT Clause, tells me all I need to know. Thanks. – DatsunBing Feb 01 '13 at 00:54

DiscoInfiltrator · Answer 2 · 2013-02-02T15:45:30.327

1

You can use a nested query:

SELECT node_id  FROM 
(
SELECT node_id FROM users LIMIT 100
) u 
ORDER BY node_id

edited Feb 02 '13 at 15:45

answered Jan 31 '13 at 23:01

DiscoInfiltrator

1,989
1
18
21

this is slower than a join – nathan hayfield Jan 31 '13 at 23:05
How is this slower than your join? You use a join and a nested query, while I only use a nested query. As far as I can tell, your solution just adds on top of mine. – DiscoInfiltrator Jan 31 '13 at 23:13
Yes, but you're joining on a subquery, which isn't the same as what that post describes. – DiscoInfiltrator Jan 31 '13 at 23:21
did you do a test to see which is faster? – nathan hayfield Jan 31 '13 at 23:23
No, but I'd like to. I don't have access to a large enough db at the moment so I'll have to test it out tomorrow. – DiscoInfiltrator Jan 31 '13 at 23:25

Should ORDER BY change the result set?

2 Answers2