Hi,

I have a question about a query which I want to share with you. Maybe
someone knows how this strange behavior occurs.

Here is what I do:

SELECT top 1 actstage FROM v_OpportunityFase WHERE oppId = 247

This produces the result 4, which in my case is correct.

And now the following query:

SELECT ISNULL((SELECT top 1 actstage FROM v_OpportunityFase WHERE
oppId = 247), 7)

This query produces the result 5, this is very unexpected.

Does someone know why these two queries do not produce the same
result?

Thanks,
Rik Moed

Hi

TOP does not really make any sense unless you have the results ordered! SQL
Server does not guarantee order unless and ORDER BY clause is included. See
Books online: "ORDER BY clause, compatibility issues"


John
"Rik Moed" <rik.moed@capgemini.com> wrote in message
news:d57c2e75.0406160713.38f4390a@posting.google.c om...
> Hi,
>
> I have a question about a query which I want to share with you. Maybe
> someone knows how this strange behavior occurs.
>
> Here is what I do:
>
> SELECT top 1 actstage FROM v_OpportunityFase WHERE oppId = 247
>
> This produces the result 4, which in my case is correct.
>
> And now the following query:
>
> SELECT ISNULL((SELECT top 1 actstage FROM v_OpportunityFase WHERE
> oppId = 247), 7)
>
> This query produces the result 5, this is very unexpected.
>
> Does someone know why these two queries do not produce the same
> result?
>
> Thanks,
> Rik Moed

Queries do not returned ordered results unless you explicitly request it
by using an ORDER BY clause.

Zach

Rik Moed wrote:
> Hi,
>
> I have a question about a query which I want to share with you. Maybe
> someone knows how this strange behavior occurs.
>
> Here is what I do:
>
> SELECT top 1 actstage FROM v_OpportunityFase WHERE oppId = 247
>
> This produces the result 4, which in my case is correct.
>
> And now the following query:
>
> SELECT ISNULL((SELECT top 1 actstage FROM v_OpportunityFase WHERE
> oppId = 247), 7)
>
> This query produces the result 5, this is very unexpected.
>
> Does someone know why these two queries do not produce the same
> result?
>
> Thanks,
> Rik Moed

> Queries do not returned ordered results unless you explicitly request it
> by using an ORDER BY clause.



Ok,
But the results are ordered in the view v_OpportunityFase.

Rik

>
> Zach
>
> Rik Moed wrote:
> > Hi,
> >
> > I have a question about a query which I want to share with you. Maybe
> > someone knows how this strange behavior occurs.
> >
> > Here is what I do:
> >
> > SELECT top 1 actstage FROM v_OpportunityFase WHERE oppId = 247
> >
> > This produces the result 4, which in my case is correct.
> >
> > And now the following query:
> >
> > SELECT ISNULL((SELECT top 1 actstage FROM v_OpportunityFase WHERE
> > oppId = 247), 7)
> >
> > This query produces the result 5, this is very unexpected.
> >
> > Does someone know why these two queries do not produce the same
> > result?
> >
> > Thanks,
> > Rik Moed

Moed (rikmoed@wanadoo.nl) writes:
> But the results are ordered in the view v_OpportunityFase.

They are not. The view may include an ORDER BY clause, and the view
may even be an indexed and thus materialized view. But by definition,
data is not stored in order in a relational database, but data is
stored as unordered sets. Physically there may be an order, but
conceptually there is not. The only way to get data in a certain
order from the database, is to use an ORDER BY clause.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp