Should I use 'ORDER BY' if field is already indexed? 3

[steve728]

If I already have the field "refno" indexed in the table, does it make any difference if I add the "ORDER BY refno" clause in the SQL statement? I've been told that it is not necessary because of the table already being indexed. Actually it will slow down the search a bit. Is this true?

Sample: "SELECT refno,[name] FROM table WHERE refno = '0123' ORDER BY refno;"

Thanks in advance,

Steve

 

[Golom]

Records in a SQL query are not guaranteed to be returned in any particular order (regardless of indexes) if you don't have an ORDER BY clause. While records in a simple one-table query are usually returned in primary key order, that's technically not guaranteed. In short, if ordering of records is important then always use an ORDER BY clause.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[PHV]

WHERE refno = '0123' ORDER BY refno
As you select an unique value the order by clause is useless ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[steve728]

Wow guys! I'm confused now. One says "Yes" the other say "No"????? Please clarify.

 

[PHV]

Golom is right.
I just said that your posted example was meaningless.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Golom]

We're both right!

What PHV is saying is that the SQL you presented will have the same value for "RefNo" in every row. Because of that ORDERing by a group of identical values has no effect.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[straybullet]

Golom is not actually saying "Yes", he was simply explaining the advantage of using the Order By clause versus not using it.

PHV is pointing out that in the SQL you posted, the inclusion of a unique value as one of the fields (together with the Order By) wouldnt work as you would be trying to sort the records by the same value. In other words, you cannot sort a list of people by last name if they are all "Wilson".

Let them hate - so long as they fear... Lucius Accius

 

[steve728]

Thank you so much for your quick replies. I understand what you mean now. In my case the refno of '0123' is not unique record value. I should have used a more obvious field name.

Thanks again and again! You all get stars! You're always there to help us!

Steve