Intermittent ORDER BY not functionning

Hi!
I have this query running well, except that when
executing some times, the result change and doesn't start
with the urgence24 column order as requested!!! It's
really strange. Does somenone can help me or tell me where
to find?
Does the SELECT INTO with temporary table can make a
problem?
SELECT noLIFNR,
noLIFNR [LIFNR],
raisonSociale,
CASE urgence24 WHEN 1 THEN 'exclamation.gif'
ELSE 'spacer.gif' END AS urgence24,
indRegionRespVentes + '-' + telRespVentes AS
telRespVentes,
respVentes,
indRegion1 + '-' + tel1 AS tel1,
ville,
nomRegion,
nomMrc
INTO #Temp
FROM vFournisseurs
WHERE actif = 1
and version = 1
ORDER BY urgence24 ASC,
raisonSociale ASC,
1 ASC
select * from #temp
Thanks in advance
DavidDavid Parenteau wrote:
> Hi!
> I have this query running well, except that when
> executing some times, the result change and doesn't start
> with the urgence24 column order as requested!!! It's
> really strange. Does somenone can help me or tell me where
> to find?
> Does the SELECT INTO with temporary table can make a
> problem?
Yes. While ORDER BY will order the results selected, the insertion order
into the temp table is not guaranteed.
<snip>
> ORDER BY urgence24 ASC,
> raisonSociale ASC,
> 1 ASC
> select * from #temp
The only way to guarantee a particular order when selecting rows is to use
an ORDER BY clause in the SELECT statement that retrieves the rows:
select * from #temp
ORDER BY urgence24 ASC,
raisonSociale ASC,
1 ASC
HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||IIRC, your technique is not guaranteed. There is a thread with the subject
"order by and identity" that recently discussed various techniques for doing
this, one of which is currently "guaranteed" according to MS.
"David Parenteau" <david.parenteau@.compuware.com> wrote in message
news:0d8401c503ad$94e0e780$a601280a@.phx.gbl...
> Hi!
> I have this query running well, except that when
> executing some times, the result change and doesn't start
> with the urgence24 column order as requested!!! It's
> really strange. Does somenone can help me or tell me where
> to find?
> Does the SELECT INTO with temporary table can make a
> problem?
> SELECT noLIFNR,
> noLIFNR [LIFNR],
> raisonSociale,
> CASE urgence24 WHEN 1 THEN 'exclamation.gif'
> ELSE 'spacer.gif' END AS urgence24,
> indRegionRespVentes + '-' + telRespVentes AS
> telRespVentes,
> respVentes,
> indRegion1 + '-' + tel1 AS tel1,
> ville,
> nomRegion,
> nomMrc
> INTO #Temp
> FROM vFournisseurs
> WHERE actif = 1
> and version = 1
> ORDER BY urgence24 ASC,
> raisonSociale ASC,
> 1 ASC
> select * from #temp
>
> Thanks in advance
> David|||Wow... I have read some part of that thread, but my
example does'nt use a IDENTITY column. How it applies the
same way? Does the problem is the SELECT INTO that don't
insert the rows after the ORDER BY occurs?
I need to give an answer to my client.
Thanks!
David

>--Original Message--
>IIRC, your technique is not guaranteed. There is a
thread with the subject
>"order by and identity" that recently discussed various
techniques for doing
>this, one of which is currently "guaranteed" according to
MS.
>"David Parenteau" <david.parenteau@.compuware.com> wrote
in message
>news:0d8401c503ad$94e0e780$a601280a@.phx.gbl...
start
where
>
>.
>|||> Does the problem is the SELECT INTO that don't
> insert the rows after the ORDER BY occurs?
A table should be treated as an unordered set of rows. You need to specify
ORDER BY when selecting *from* the table in order to guarantee a particular
sequence. Insertion sequence isn't relevant in this case.
Hope this helps.
Dan Guzman
SQL Server MVP
"David Parenteau" <david.parenteau@.compuware.com> wrote in message
news:0dcc01c503b2$c5b96210$a601280a@.phx.gbl...
> Wow... I have read some part of that thread, but my
> example does'nt use a IDENTITY column. How it applies the
> same way? Does the problem is the SELECT INTO that don't
> insert the rows after the ORDER BY occurs?
> I need to give an answer to my client.
> Thanks!
> David
>
> thread with the subject
> techniques for doing
> MS.
> in message
> start
> where|||My understanding is that the guaranteed technique involves the creation of a
temp table with an identity column and the use of an insert/select
statement. However, the topic of the referenced thread is not exactly the
same as the issue you are experiencing (now that I read the post a bit more
carefully). Bob's post is actually more appropriate.
I don't particularly understand why you use a temp table when you can simply
return the result set generated select/into statement (just omit the "into"
part). In any event, you should always consider a table as an unordered set
of rows and that a select statement without an order by clause is never
guaranteed to return the rows in any consistent order. I believe that is
your fundamental flaw. In addition, I also find fault with the use of the
asterisk ("*"), especially if this is production code. Both of these issues
are frequently discussed in the newsgroup.
"David Parenteau" <david.parenteau@.compuware.com> wrote in message
news:0dcc01c503b2$c5b96210$a601280a@.phx.gbl...
> Wow... I have read some part of that thread, but my
> example does'nt use a IDENTITY column. How it applies the
> same way? Does the problem is the SELECT INTO that don't
> insert the rows after the ORDER BY occurs?
> I need to give an answer to my client.
> Thanks!
> David
>
> thread with the subject
> techniques for doing
> MS.
> in message
> start
> where|||David,
Your concern is not with the order in which rows
are inserted into #temp. Your concern is with the order
of rows in the result set of the select query:
SELECT * FROM #temp
At least that's what it sounds like - sometimes you see
the resulting rows in an order you don't want.
The easy solution, and the only guaranteed solution, if
you need to see results in a particular order, is to add
an ORDER BY clause. You will always get the order
you want if you execute
SELECT * FROM #temp
ORDER BY urgence24, raisonSociale, noLIFNR
The rows in a table are not in any order - they are
more like apples in a bag. They don't automatically
fall out of the bag in the exact order they went into
the bag.
In this particular case, the order in which the
rows were inserted is irrelevant, since you have no
IDENTITY column to record that order. The issue
here is the order or results from your SELECT query,
and that can be controlled with ORDER BY. Parallelism,
indexes, concurrency, and many other factors can change
the order in which the rows are returned, if there is no
ORDER BY clause.
Steve Kass
Drew University
David Parenteau wrote:
>Wow... I have read some part of that thread, but my
>example does'nt use a IDENTITY column. How it applies the
>same way? Does the problem is the SELECT INTO that don't
>insert the rows after the ORDER BY occurs?
>I need to give an answer to my client.
>Thanks!
>David
>
>
>thread with the subject
>
>techniques for doing
>
>MS.
>
>in message
>
>start
>
>where
>|||Ok, thanks for the *, I practice this too. The code here
was only the first part of all my code.
That table is used later to get only the record in the
middle of the entire result set (a particular page).
I need the order, as adviced here, only after that SELECT
INTO, to get always the same rows in the same page (row
460 to 469 to get the page 46 for example)
Thanks a lot of your help all you guys!

>--Original Message--
>My understanding is that the guaranteed technique
involves the creation of a
>temp table with an identity column and the use of an
insert/select
>statement. However, the topic of the referenced thread
is not exactly the
>same as the issue you are experiencing (now that I read
the post a bit more
>carefully). Bob's post is actually more appropriate.
>I don't particularly understand why you use a temp table
when you can simply
>return the result set generated select/into statement
(just omit the "into"
>part). In any event, you should always consider a table
as an unordered set
>of rows and that a select statement without an order by
clause is never
>guaranteed to return the rows in any consistent order. I
believe that is
>your fundamental flaw. In addition, I also find fault
with the use of the
>asterisk ("*"), especially if this is production code.
Both of these issues
>are frequently discussed in the newsgroup.
>"David Parenteau" <david.parenteau@.compuware.com> wrote
in message
>news:0dcc01c503b2$c5b96210$a601280a@.phx.gbl...|||Well that clarifies things. Usually questions about a particular technique
generate "better" answers when the reason driving its usage is included.
Perhaps you will find the following link useful:
"David Parenteau" <david.parenteau@.compuware.com> wrote in message
news:072801c503b7$34654e00$a401280a@.phx.gbl...
> Ok, thanks for the *, I practice this too. The code here
> was only the first part of all my code.
> That table is used later to get only the record in the
> middle of the entire result set (a particular page).
> I need the order, as adviced here, only after that SELECT
> INTO, to get always the same rows in the same page (row
> 460 to 469 to get the page 46 for example)
> Thanks a lot of your help all you guys!
>
> involves the creation of a
> insert/select
> is not exactly the
> the post a bit more
> when you can simply
> (just omit the "into"
> as an unordered set
> clause is never
> believe that is
> with the use of the
> Both of these issues
> in message
>|||Thanks, I will go to that page :)
David
>--Original Message--
>slipped ... and that link is
http://www.aspfaq.com/show.asp?id=2120
>"Scott Morris" <bogus@.bogus.com> wrote in message
>news:eKW$No7AFHA.1400@.TK2MSFTNGP11.phx.gbl...
particular
>technique
usage is included.
in message
here
SELECT
(row
thread
read
table
table
by
order. I
code.
wrote
>
>.
>