By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,584 Members | 1,750 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,584 IT Pros & Developers. It's quick & easy.

query question

P: n/a
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
Jul 20 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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" <ri******@capgemini.com> wrote in message
news:d5**************************@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

Jul 20 '05 #2

P: n/a
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

Jul 20 '05 #3

P: n/a
> 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

Jul 20 '05 #4

P: n/a
Moed (ri*****@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.