Thanks Ann for your explanations !


________________________________
From: Ann Harrison <aharrison@...>
To: firebird-support@yahoogroups.com
Sent: Thursday, September 13, 2012 12:21 AM
Subject: Re: [firebird-support] Strange SELECT FIRST 1


 

On Wed, Sep 12, 2012 at 3:25 PM, Mr. John <mr_johnmr@...> wrote:

> Hi Ann,thanks for your tips,so SELECT FIRST 1 is slower than
> simple SELECT ?
>

Not in general, but why complicate things?

> Why to add ORDER BY ?
>

That gives you a chance of getting the same record every time. Lets
assume you have a table of pets, and in it you've got descriptions of
hundreds of animals.

select first 1 Name from Pets where Species = 'cat'

would probably get the same name on three sequential tries because Firebird
is retrieving the results in storage order. But if some one deletes an
early 'dog' and someone else inserts a cat, that cat may well take the dog
slot and become the first cat in storage order. So suddenly instead of
'Methos', you get 'Amanda'.

select first 1 Name from Pets where Species = 'cat' order by DateOfBirth

If you've got an index on DateOfBirth, it's very quick.

Good luck,

Ann

[Non-text portions of this message have been removed]




[Non-text portions of this message have been removed]