Re: Establishing Precedence In ORDERBY Condition Causing Problems.

pbd22 (dushkin@xxxxxxxxx) writes:
1) and 3) makes perfect sense. The second I cannot understand. As far
as I understand, this query is likely to return multiple rows. But which
rows it returns - we don't know. Since @sort is NULL at this point,
the ORDER BY has no effect. It's probably the explanation to why your
@sort goes bad, but I can't say what you should do to correct, because
I have very little clue how your tables are related.

It sounds to me like the middle code block is causing me my errors but
I am not sure what I am doing wrong still. Would you mind taking a look
at an Entity Relationship Diagram? It might give you a better
understanding of how my data is designed and for what purpose. If that
is OK, I'll email it to you via your address provided here.

And I don't know what you are doing wrong, because I don't know what you
are trying to achieve.

There is a common recommendation for this type of questions, and that is
that you post:

o CREATE TABLE statements for your tables.
o INSERT statements with sample data.
o The resired result given the sample.

Now, since your original query had some 7-8 tables whereof several repeated
in the FROM clause, you will need to simplify the problem down to the
core.

If I understand this correctly, this is about saved searches, so the
clou is certainly SavedSearches, but try to invent a similar case with
fewer tables. Yes, that may take you some time, but I rather have
you doing that than showing me an E-R diagramme that may not help me
to understand what you are trying to achieve. To wit, I am not sure
that you understand yourself. But if you spend some time with a simpler
case then maybe you get can get that understanding.



--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: Establishing Precedence In ORDERBY Condition Causing Problems.
    ... this query is likely to return multiple rows. ... Since @sort is NULL at this point, ... since your original query had some 7-8 tables whereof several repeated ... by commenting out the following code, ...
    (comp.databases.ms-sqlserver)
  • Re: Establishing Precedence In ORDERBY Condition Causing Problems.
    ... That query looks very much like the SELECT in the procedure you ... The ORDER BY CASE @sort is supposed to only tell SQL to return data ... far as I can call you have <bigquery> thrice in your procedure: ... I was SQL Server 2000 when I wrote this SPROC but we have since ...
    (comp.databases.ms-sqlserver)
  • Re: What is "order by" using to sort ...?
    ... Your query will, as Aaron demonstrates, sort on the first characters. ... Pro SQL Server 2000 Database Design - ...
    (microsoft.public.sqlserver.programming)
  • Re: What GROUP BY is not...
    ... I really don't know about what Jet does in the step "group" of its query ... MS SQL Server is more detailed and uses the index (if it exists, ... search) than to sort the whole initial set first, ...
    (microsoft.public.access.queries)
  • Re: ORDER BY in VIEW not working
    ... I sort of expected this kind of response, but since it serves utterly no ... query results can always qualify as relations. ... standard SQL and in general also SQL Server DO define the ... result is exposed as a cursor for row-bound operations at the client ...
    (comp.databases.ms-sqlserver)