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
>
>.
>

Interactive Sorting

I know this is not available now, but is it plan in the near future:

I want to be able to set the initial sort order on the interactive sort.

for example, I have a report sorted Asc by Rank. If I click on the interactive sort button on the Rank column, it resorts ascending... totally pointless, and my customers point this out on a weekly basis...

Any plans to add this feature? If not, I will go to connect and suggest it.

Also, does anyone else find this annoying?

Thanks!

BobP

So you are saying that when you click the sort button on a report that is already sorted ascending, you want it to sort descending and vice versa?

Right click the field you want to interactively sort. Click Properties. Go to the interactive sort tab.

Is this not what you want?

By the way, how does this work? I'm trying to get one of my drilldowns to interactively sort by transaction amount. It won't seem to sort it. I think I just don't have it set up properly.

|||

It is exactly what i want, and it works great EXCEPT... If the report is sorted by default on Column A ascending, and I click on the sort button on column A, it tries to sort it asc first. I have to push it twice to sort desc.

To get it to sort, you have to select the correct grouping to sort, and the correct grouping for the sort expression.

Generally, if I have 1 group, I can sort on the table level, and leave the bottom selection default.

BobP

|||

BobP - BIM wrote:

It is exactly what i want, and it works great EXCEPT... If the report is sorted by default on Column A ascending, and I click on the sort button on column A, it tries to sort it asc first. I have to push it twice to sort desc.

To get it to sort, you have to select the correct grouping to sort, and the correct grouping for the sort expression.

Generally, if I have 1 group, I can sort on the table level, and leave the bottom selection default.

BobP

I got it to sort for me. But I see what you're talking about now. That is somewhat annoying. Especially when you go sort happy.

|||Is there any way to change the default order of the button? I need a report to display descending first, then ascending if clicked a second time. However, I am unable to find any setting in the report designer that allows me to change this.|||

That's what this thread is about. No, it does not exist now.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=249537

BobP

Interactive Sort

I'm using the interactive sort in a report. Can I pass in the previously
sorted column so that a user could build a sort order of their choice using
two or more columns by appending their prior selections to the sort?Hi Brent,
Thanks for your posting.
From your descriptions, I understood you would like to know whether it is
possible to specify two or more columns to make interactive sorting. If I
have misunderstood your concern, please feel free to point it out.
Based on my knowledge, we are not able to use previously sorted column. To
make priority of sorted columns, you are encouraged to Parameterized
Sorting. Specify two or more parameters and then combile them in a dynamic
query. For more detailed information, see section "Parameterized Sorting"
below
Sorting Data
http://msdn2.microsoft.com/en-us/library/ms157313.aspx
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
Others: https://partner.microsoft.com/US/technicalsupport/supportoverview/
If you are outside the United States, please visit our International
Support page: http://support.microsoft.com/common/international.aspx
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.