Order of conditions in a query

I have a query with many (approximately, 30) conditions, such as:

select ....... from table1 join table2 on ( (table1.field1 = table2.field1 OR table1.filed1 IS NULL) AND (table1.field2 = table2.field2 OR table1.filed2 IS NULL) )

My question is:

In C++ or C#, when I write a condition like this, say, in an IF or WHILE, I know that I would be better off specifying the IS NULL (well, == null, to be precise) first, and use | instead of ||. In that case, the first condition (equality to null) is checked first, it's fast, and if it's not satisfied, the control flow goes to the next statement.

The question is, is there the same rule in T-SQL?

I mean, if I put the "... IS NULL" first, and then "OR ... = ...", will the query run faster than if I write it the other way around (that is, "... = ... OR ... IS NULL")?

This is very important to me, because most of those fields are VARCHAR, and due to some business rules, I can't change them to numerics etc, which would be compared much faster than text. So, even if I use full text search, I still need to find a way to optimize the query for performance...

By the way, I know that I can put those conditions in the WHERE clause, but as far as I know it won't make much of a difference for performance. So, my question is primarily about the order of conditions, in which SQL Server constructs its query plan.

[Edited:] In other words, what runs faster: comparing varchar to null or comparing varchars? And does it make a difference if I switch their places in my sql script?

We are using SQL Server 2000 SP4, Standard Edition. [Dev edition on the dev machine.]

Could someone kindly advise me on this, please?

Thank you ever so much.

The way you have that written, can't you do a right join and omit the IS NULL condition?|||The order the WHERE clause is processed is "undefined" and "not guaranteed". So don't try to write something that is based on process order.

IS NULL is very fast compared to varchar equals. But the optimizer is going to "pick" how to best process the query and may not do it the same way every time.|||

The way you have that written, can't you do a right join and omit the IS NULL condition?

I'd love to, but it's an "either-or" from a business logic, and I'm not returning a set of rows, - this query is actually a part of the matching logic (a Notify function) for a Notification Services application. I'm sorry, I should have mentioned that.

Thank you.

sql

Order for conditions to be processed

what is order in which conditions are processed for sql query i.e for
select * from table1, table2 where cond1 and cond2 and cond3 which condition will be processed first (i.e. for optimination purpose condition cutting down max no. of row shud be placed first or last?)Originally posted by kiranghag
what is order in which conditions are processed for sql query i.e for
select * from table1, table2 where cond1 and cond2 and cond3 which condition will be processed first (i.e. for optimination purpose condition cutting down max no. of row shud be placed first or last?)

If all the conditions are AND'ed then the order will be left to right, if the first condition is false the entire thing will be false so the remaining conditions would not be processed.
And if the conditions are OR'ed then if the first condition is true then all the conditions are true.
Also adding parenthesis decides which conditions are processesed when.

Regards,
Harshal.|||Originally posted by kiranghag
what is order in which conditions are processed for sql query i.e for
select * from table1, table2 where cond1 and cond2 and cond3 which condition will be processed first (i.e. for optimination purpose condition cutting down max no. of row shud be placed first or last?)

sql has an internal parser which reorders the conditions depending on clustered/indexes and such (keys for example). Conditions that refer to the c/indexes are processed prior to the non-c/indexes.

Besides that, the order in which the other conditions are processed might get reversed, which ever suit sql best.|||Kaiowas, I think you're talking about the order in which the records are retrieved in the case of SELECT, or UPDATEed/DELETEed respectively.

harshal's statement pretty much summarizes what the optimizer does and how to control it.

Order converted dates in union query

I have the following as part of a union query:

CONVERT(CHAR(8), r.RRDate, 1) AS [Date]

I also want to order by date but when I do that it doesn't order correctly because of the conversion to char data type (for example, it puts 6/15/05 before 9/22/04 because it only looks at the first number(s) and not the year). If I try to cast it back to smalldatetime in the order by clause it tells me that ORDER BY items must appear in the select list if the statement contains a UNION operator. I get the same message if I try putting just "r.RRDate" in the ORDER BY clause. It's not that big of a deal - I can lose the formatting on the date if I need to to get it to sort correctly, but this query gets used frequently and I'd like to keep the formatting if possible.

Thanks,

Dave

Do you really require UNION operator? If the results of each SELECT statement in the UNION is distinct then use UNION ALL. This will also provide better performance since it doesn't do the duplicate elimination step. And if you use UNION ALL then you can use the column name "r.RRDate" in the ORDER BY clause. If you need to use UNION then only way is to specify the column in the SELECT list also if you want it in the ORDER BY clause. Lastly, is there any reason for your to format the date in the query itself. It is usually unnecessary work to do this on the server-side. It is best to send the date value as is and format on the client. Alternatively, you can use a style which is universal and will preserve sorting for example like the ISO unseparated date format (style 112: YYYYMMDD) or ISO 8601 datetime format (style 126: YYYY-MM-DDThh:mm:ss.nnn). Using language dependent style format is always confusing and can cause errors when you try to use it as is in a different system that has a different language setting for example.

ORDER BY with SELECT DISTINCT

Hi,
I am trying to sort a SELECT DISTINCT query using ORDER BY, but get an error
message saying that the ORDER BY needs to be included in the SELECT
statemeny.
Does anyone have a sample on how to sort a SELECT DISTINCT query ?
Niclas"Niclas" <lindblom_niclas@.hotmail.com> wrote in message
news:ubdCBujQGHA.4900@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am trying to sort a SELECT DISTINCT query using ORDER BY, but get an
> error message saying that the ORDER BY needs to be included in the SELECT
> statemeny.
> Does anyone have a sample on how to sort a SELECT DISTINCT query ?
> Niclas
>
The columns you want to order on have to be included in the SELECT list.
Here's why:
CREATE TABLE tbl (x INTEGER NOT NULL, z INTEGER NOT NULL, PRIMARY KEY
(x,z));
INSERT INTO tbl (x,z) VALUES (100,1);
INSERT INTO tbl (x,z) VALUES (100,2);
INSERT INTO tbl (x,z) VALUES (200,0);
INSERT INTO tbl (x,z) VALUES (200,4);
SELECT DISTINCT x FROM tbl ORDER BY z;
Result:
Server: Msg 145, Level 15, State 1, Line 9
ORDER BY items must appear in the select list if SELECT DISTINCT is
specified.
Can you explain how SQL Server could return X ordered by Z in this example?
Should 100 come first or should 200 come first? There is no single answer so
that's why it has to be disallowed unless Z is in the SELECT list. Example:
SELECT DISTINCT x,z FROM tbl ORDER BY z;
The problem is with your specification rather than with SQL Server. You
haven't given us a clue about what you really want to sort on so here are a
couple of possibilities using the above example data. Notice you'll get two
different orders:
SELECT x
FROM tbl
GROUP BY x
ORDER BY MIN(z);
x
--
200
100
(2 row(s) affected)
SELECT x
FROM tbl
GROUP BY x
ORDER BY MAX(z);
x
--
100
200
(2 row(s) affected)
Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Order By with Query then bottom border

Reporting Services 2000
I have a SQL query that is properly sorting a list of items I have by using
the order by clause. I created a report using the report wizard and didn't
use any fields to group by because I already have the list in the correct
order. I did use RS to keep each team on it's own page. How can I have a
border or bgcolor change when the owner of a project changes. For example
I'd like a border after Bob and before John.
Sample Data:
Team Owner Project
NY bob Remote Access
NY bob Server Reboot
NY John Network Upgrade
Here is my order by clause:
ORDER BY Team, CASE WHEN Owner = Team THEN 1 ELSE 2 ENDDid you try to use an expression to set border width?
=IIF(Fields!OwnerValue = Previous(Fields!Owner.Value),1pt,0pt)
"Colin" wrote:
> Reporting Services 2000
> I have a SQL query that is properly sorting a list of items I have by using
> the order by clause. I created a report using the report wizard and didn't
> use any fields to group by because I already have the list in the correct
> order. I did use RS to keep each team on it's own page. How can I have a
> border or bgcolor change when the owner of a project changes. For example
> I'd like a border after Bob and before John.
> Sample Data:
> Team Owner Project
> NY bob Remote Access
> NY bob Server Reboot
> NY John Network Upgrade
> Here is my order by clause:
> ORDER BY Team, CASE WHEN Owner = Team THEN 1 ELSE 2 END
>
>|||I didn't try that. Thank you.
Where can I find a list of all the functions that can be called?
"isaak" <isaak.peretsman at usa.dupont.com (no spam)> wrote in message
news:E28C729B-AE19-4ADF-9983-233B666CE5D1@.microsoft.com...
> Did you try to use an expression to set border width?
> =IIF(Fields!OwnerValue = Previous(Fields!Owner.Value),1pt,0pt)
> "Colin" wrote:
>> Reporting Services 2000
>> I have a SQL query that is properly sorting a list of items I have by
>> using
>> the order by clause. I created a report using the report wizard and
>> didn't
>> use any fields to group by because I already have the list in the correct
>> order. I did use RS to keep each team on it's own page. How can I have
>> a
>> border or bgcolor change when the owner of a project changes. For
>> example
>> I'd like a border after Bob and before John.
>> Sample Data:
>> Team Owner Project
>> NY bob Remote Access
>> NY bob Server Reboot
>> NY John Network Upgrade
>> Here is my order by clause:
>> ORDER BY Team, CASE WHEN Owner = Team THEN 1 ELSE 2 END
>>
>>|||Can I confuse this just a tad more. What if I only want a border the very
first time the owner changes and don't need a border after that?
"Colin" <legendsfan@.spamhotmail.com> wrote in message
news:O1DEBwKwGHA.4460@.TK2MSFTNGP04.phx.gbl...
>I didn't try that. Thank you.
> Where can I find a list of all the functions that can be called?
> "isaak" <isaak.peretsman at usa.dupont.com (no spam)> wrote in message
> news:E28C729B-AE19-4ADF-9983-233B666CE5D1@.microsoft.com...
>> Did you try to use an expression to set border width?
>> =IIF(Fields!OwnerValue = Previous(Fields!Owner.Value),1pt,0pt)
>> "Colin" wrote:
>> Reporting Services 2000
>> I have a SQL query that is properly sorting a list of items I have by
>> using
>> the order by clause. I created a report using the report wizard and
>> didn't
>> use any fields to group by because I already have the list in the
>> correct
>> order. I did use RS to keep each team on it's own page. How can I have
>> a
>> border or bgcolor change when the owner of a project changes. For
>> example
>> I'd like a border after Bob and before John.
>> Sample Data:
>> Team Owner Project
>> NY bob Remote Access
>> NY bob Server Reboot
>> NY John Network Upgrade
>> Here is my order by clause:
>> ORDER BY Team, CASE WHEN Owner = Team THEN 1 ELSE 2 END
>>
>>
>|||For the function list go to SqlServer Books Online, "Using Functions in
Reporting Services" is the name of the topic.
To show the border the first time only, try this
=IIF(Fields!OwnerValue <> Previous(Fields!Owner.Value) AND
Previous(Fields!Owner.Value)= Min(Fields!Owner.Value),1pt,0pt)
"Colin" wrote:
> Can I confuse this just a tad more. What if I only want a border the very
> first time the owner changes and don't need a border after that?
> "Colin" <legendsfan@.spamhotmail.com> wrote in message
> news:O1DEBwKwGHA.4460@.TK2MSFTNGP04.phx.gbl...
> >I didn't try that. Thank you.
> >
> > Where can I find a list of all the functions that can be called?
> >
> > "isaak" <isaak.peretsman at usa.dupont.com (no spam)> wrote in message
> > news:E28C729B-AE19-4ADF-9983-233B666CE5D1@.microsoft.com...
> >> Did you try to use an expression to set border width?
> >>
> >> =IIF(Fields!OwnerValue = Previous(Fields!Owner.Value),1pt,0pt)
> >>
> >> "Colin" wrote:
> >>
> >> Reporting Services 2000
> >> I have a SQL query that is properly sorting a list of items I have by
> >> using
> >> the order by clause. I created a report using the report wizard and
> >> didn't
> >> use any fields to group by because I already have the list in the
> >> correct
> >> order. I did use RS to keep each team on it's own page. How can I have
> >> a
> >> border or bgcolor change when the owner of a project changes. For
> >> example
> >> I'd like a border after Bob and before John.
> >>
> >> Sample Data:
> >> Team Owner Project
> >> NY bob Remote Access
> >> NY bob Server Reboot
> >> NY John Network Upgrade
> >>
> >> Here is my order by clause:
> >> ORDER BY Team, CASE WHEN Owner = Team THEN 1 ELSE 2 END
> >>
> >>
> >>
> >>
> >
> >
>
>

ORDER BY with alias

Hi NG,
in my following query I get the error "Invalid column name 'price'" but
everything seems ok:
SELECT row_number() over (order by price) as row_num,
dbo.getProductPrice('1234') as price
MS describes the use of aliases in ORDER BY
http://msdn2.microsoft.com/ms188385.aspx
Any ideas?
Thanks
Andre ScheiberleAndre Scheiberle wrote:
> Hi NG,
> in my following query I get the error "Invalid column name 'price'" but
> everything seems ok:
> SELECT row_number() over (order by price) as row_num,
> dbo.getProductPrice('1234') as price
> MS describes the use of aliases in ORDER BY
> http://msdn2.microsoft.com/ms188385.aspx
> Any ideas?
> Thanks
> Andre Scheiberle
ORDER BY in a query is different to ORDER BY in a ranking function. In
the latter case you can only reference base columns, not aliases. Try
the following (I assume you omitted the FROM clause in error).
SELECT ROW_NUMBER() OVER (ORDER BY price) AS row_num, price
FROM
(SELECT dbo.getProductPrice('1234')
FROM tbl) AS T(price) ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||does dbo.getProductPrice('1234')
return a table or a scalar value?
--
"Andre Scheiberle" wrote:

> Hi NG,
> in my following query I get the error "Invalid column name 'price'" but
> everything seems ok:
> SELECT row_number() over (order by price) as row_num,
> dbo.getProductPrice('1234') as price
> MS describes the use of aliases in ORDER BY
> http://msdn2.microsoft.com/ms188385.aspx
> Any ideas?
> Thanks
> Andre Scheiberle
>|||ok, in this example it works, but in my Application I use this Query:
SELECT ROW_NUMBER() OVER (ORDER BY price) AS row_num, price, article
FROM
products,
(SELECT dbo.getProductPrice(products.article)M tbl) AS T(price) ;
Now an error appears "The multi-part identifier "products.article" could not
be bound."
Thanks
Andre
"David Portas" wrote:

> Andre Scheiberle wrote:
> ORDER BY in a query is different to ORDER BY in a ranking function. In
> the latter case you can only reference base columns, not aliases. Try
> the following (I assume you omitted the FROM clause in error).
> SELECT ROW_NUMBER() OVER (ORDER BY price) AS row_num, price
> FROM
> (SELECT dbo.getProductPrice('1234')
> FROM tbl) AS T(price) ;
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||The table return a scalar value
"Omnibuzz" wrote:
> does dbo.getProductPrice('1234')
> return a table or a scalar value?
> --
>
>
> "Andre Scheiberle" wrote:
>|||try this then.
SELECT ROW_NUMBER() OVER (ORDER BY price) AS row_num, price, article
from
(SELECT dbo.getProductPrice(article), article
FROM
products) AS T(price, article)
"Omnibuzz" wrote:
> does dbo.getProductPrice('1234')
> return a table or a scalar value?
> --
>
>
> "Andre Scheiberle" wrote:
>|||Andre Scheiberle wrote:
> ok, in this example it works, but in my Application I use this Query:
> SELECT ROW_NUMBER() OVER (ORDER BY price) AS row_num, price, article
> FROM
> products,
> (SELECT dbo.getProductPrice(products.article)M tbl) AS T(price) ;
> Now an error appears "The multi-part identifier "products.article" could n
ot
> be bound."
> Thanks
> Andre
>
You can't reference a table from the outer query in a derived table
subquery. Try this (assuming your function is scalar):
SELECT ROW_NUMBER() OVER (ORDER BY price) AS row_num,
price, article
FROM
(SELECT dbo.getProductPrice(article), article
FROM products) AS T(price,article) ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Here is how a SELECT works in SQL ... at least in theory. Real
products will optimize things, but the code has to produce the same
results.
a) Start in the FROM clause and build a working table from all of the
joins, unions, intersections, and whatever other table constructors are
there. The <table expression> AS <correlation name> option allows you
give a name to this working table which you then have to use for the
rest of the containing query.
b) Go to the WHERE clause and remove rows that do not pass criteria;
that is, that do not test to TRUE (i.e. reject UNKNOWN and FALSE). The
WHERE clause is applied to the working set in the FROM clause.
c) Go to the optional GROUP BY clause, make groups and reduce each
group to a single row, replacing the original working table with the
new grouped table. The rows of a grouped table must be group
characteristics: (1) a grouping column (2) a statistic about the group
(i.e. aggregate functions) (3) a function or (4) an expression made up
those three items. The original table no longer exists.
d) Go to the optional HAVING clause and apply it against the grouped
working table; if there was no GROUP BY clause, treat the entire table
as one group.
e) Go to the SELECT clause and construct the expressions in the list.
This means that the scalar subqueries, function calls and expressions
in the SELECT are done after all the other clauses are done. The AS
operator can also give names to expressions in the SELECT list. These
new names come into existence all at once, but after the WHERE clause,
GROUP BY clause and HAVING clause have been executed; you cannot use
them in the SELECT list or the WHERE clause for that reason.
If there is a SELECT DISTINCT, then redundant duplicate rows are
removed. For purposes of defining a duplicate row, NULLs are treated
as matching (just like in the GROUP BY).
f) Nested query expressions follow the usual scoping rules you would
expect from a block structured language like C, Pascal, Algol, etc.
Namely, the innermost queries can reference columns and tables in the
queries in which they are contained.
g) The ORDER BY clause is part of a cursor, not a query. The result
set is passed to the cursor, which can only see the names in the SELECT
clause list, and the sorting is done there. The ORDER BY clause cannot
have expression in it, or references to other columns because the
result set has been converted into a sequential file structure and that
is what is being sorted.
As you can see, things happen "all at once" in SQL, not "from left to
right" as they would in a sequential file/procedural language model. In
those languages, these two statements produce different results:
READ (a, b, c) FROM File_X;
READ (c, a, b) FROM File_X;
while these two statements return the same data:
SELECT a, b, c FROM Table_X;
SELECT c, a, b FROM Table_X;
Think about what a mess this statement is in the SQL model.
SELECT f(c2) AS c1, f(c1) AS c2 FROM Foobar;
That is why such nonsense is illegal syntax.

Order By Start_Date

Hi

Ive searched but cant find any answers for this.

In my query Ive done an ORDER BY start_date ASC

However it only orders the day not the month or year?

any ideas?

Quote:

Originally Posted by ljbuxton

Hi

Ive searched but cant find any answers for this.

In my query Ive done an ORDER BY start_date ASC

However it only orders the day not the month or year?

any ideas?

Please post the DDL scripts and query you are running, otherwise it is hard to guess what issues do you have.
start_date field may be even of type varchar with 'DD-MM-YY' format

ORDER BY specific values

Hi Everyone,
I was wondering if there is anyway to ORDER BY in a query by certain values first. I have a table with Projects and subprojects and sub-subprojects and I always want to display the parent project first. Is there anyway that I can do that.
Thanks for all your help.Could you give us a little inside information about your data-structure, eventually accompanied with some sample data and the way you would like to have it ?sql

order by slowing me down X 40

sql2k sp3
This query takes 40 seconds to run in Query Analyzer. If I
run it without the Order By it takes 0 seconds. This in
itself isnt to bizarre. But heres the catch, the Clustered
Index is on the transdate coulmn. That being the case,
shouldnt the difference be less?
select top 100 CustomerKey,transdate
from transdtl
where MerchName in ('DTV*DIRECTV SERVICE','DTV*DIRECTV
SERVICER39')
and TranCode not in (7008,7023)
Group By CustomerKey,transdate
order by transdate
TIA, ChrisR
Did you view the query plan with and without the order by? What did you
see?
http://www.aspfaq.com/
(Reverse address to reply.)
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:21a7001c45afd$08f70a50$a501280a@.phx.gbl...
> sql2k sp3
> This query takes 40 seconds to run in Query Analyzer. If I
> run it without the Order By it takes 0 seconds. This in
> itself isnt to bizarre. But heres the catch, the Clustered
> Index is on the transdate coulmn. That being the case,
> shouldnt the difference be less?
>
> select top 100 CustomerKey,transdate
> from transdtl
> where MerchName in ('DTV*DIRECTV SERVICE','DTV*DIRECTV
> SERVICER39')
> and TranCode not in (7008,7023)
> Group By CustomerKey,transdate
> order by transdate
>
> TIA, ChrisR
>
|||Theres table scans either way. But the order by query has
sorts as well.

>--Original Message--
>Did you view the query plan with and without the order
by? What did you
>see?
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"ChrisR" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:21a7001c45afd$08f70a50$a501280a@.phx.gbl...
If I[vbcol=seagreen]
Clustered
>
>.
>
|||try
Group By transdate, CustomerKey
I'm guessing that without the order by it just has to get the first 100 groups. With the order by it has to get all the groups to sort.
Have a look at the number of rows in steps in the query plan.
"ChrisR" wrote:

> sql2k sp3
> This query takes 40 seconds to run in Query Analyzer. If I
> run it without the Order By it takes 0 seconds. This in
> itself isnt to bizarre. But heres the catch, the Clustered
> Index is on the transdate coulmn. That being the case,
> shouldnt the difference be less?
>
> select top 100 CustomerKey,transdate
> from transdtl
> where MerchName in ('DTV*DIRECTV SERVICE','DTV*DIRECTV
> SERVICER39')
> and TranCode not in (7008,7023)
> Group By CustomerKey,transdate
> order by transdate
>
> TIA, ChrisR
>
|||Even just using Order By TramsDate, it still slows it down just the same.
"Nigel Rivett" <NigelRivett@.discussions.microsoft.com> wrote in message
news:1BB2D0D3-8C0D-4024-98C7-317014533102@.microsoft.com...
> try
> Group By transdate, CustomerKey
>
> I'm guessing that without the order by it just has to get the first 100
groups. With the order by it has to get all the groups to sort.[vbcol=seagreen]
> Have a look at the number of rows in steps in the query plan.
>
> "ChrisR" wrote:
|||Chris,
The query is asking for different results depending on whether you
include the ORDER BY. Without the ORDER BY, you are asking for any 100
rows from among the (Customer, transdate) pairs appearing in rows that
satisfy your MerchName and TranCode criteria. With the ORDER BY, you
are asking for a specific 100 (Customer, transdate) pairs satisfying
your MerchName and TranCode criteria - the 100 pairs that have the
earliest transdate values.
There are various scenarios where the query will be slower with an
ORDER BY clause, although I'm not sure why you are seeing a table scan
(i.e., a clustered index scan) in both situations. In addition, "Table
Scan" should not appear as an operator when the table has a clustered
index, so do you think you could post the CREATE TABLE and CREATE INDEX
statements for these tables and the estimated execution plans obtained
with the SET SHOWPLAN_TEXT ON?
Steve Kass
Drew University
ChrisR wrote:

>sql2k sp3
>This query takes 40 seconds to run in Query Analyzer. If I
>run it without the Order By it takes 0 seconds. This in
>itself isnt to bizarre. But heres the catch, the Clustered
>Index is on the transdate coulmn. That being the case,
>shouldnt the difference be less?
>
>select top 100 CustomerKey,transdate
>from transdtl
>where MerchName in ('DTV*DIRECTV SERVICE','DTV*DIRECTV
>SERVICER39')
>and TranCode not in (7008,7023)
>Group By CustomerKey,transdate
>order by transdate
>
>TIA, ChrisR
>
>
|||Yes but Ill have to repost Monday. Have a great weekend.
"Steve Kass" <skass@.drew.edu> wrote in message
news:eWYRFh5WEHA.2972@.TK2MSFTNGP12.phx.gbl...
> Chris,
> The query is asking for different results depending on whether you
> include the ORDER BY. Without the ORDER BY, you are asking for any 100
> rows from among the (Customer, transdate) pairs appearing in rows that
> satisfy your MerchName and TranCode criteria. With the ORDER BY, you
> are asking for a specific 100 (Customer, transdate) pairs satisfying
> your MerchName and TranCode criteria - the 100 pairs that have the
> earliest transdate values.
> There are various scenarios where the query will be slower with an
> ORDER BY clause, although I'm not sure why you are seeing a table scan
> (i.e., a clustered index scan) in both situations. In addition, "Table
> Scan" should not appear as an operator when the table has a clustered
> index, so do you think you could post the CREATE TABLE and CREATE INDEX
> statements for these tables and the estimated execution plans obtained
> with the SET SHOWPLAN_TEXT ON?
> Steve Kass
> Drew University
> ChrisR wrote:
>

order by slowing me down X 40

sql2k sp3
This query takes 40 seconds to run in Query Analyzer. If I
run it without the Order By it takes 0 seconds. This in
itself isnt to bizarre. But heres the catch, the Clustered
Index is on the transdate coulmn. That being the case,
shouldnt the difference be less?
select top 100 CustomerKey,transdate
from transdtl
where MerchName in ('DTV*DIRECTV SERVICE','DTV*DIRECTV
SERVICER39')
and TranCode not in (7008,7023)
Group By CustomerKey,transdate
order by transdate
TIA, ChrisRDid you view the query plan with and without the order by? What did you
see?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:21a7001c45afd$08f70a50$a501280a@.phx.gbl...
> sql2k sp3
> This query takes 40 seconds to run in Query Analyzer. If I
> run it without the Order By it takes 0 seconds. This in
> itself isnt to bizarre. But heres the catch, the Clustered
> Index is on the transdate coulmn. That being the case,
> shouldnt the difference be less?
>
> select top 100 CustomerKey,transdate
> from transdtl
> where MerchName in ('DTV*DIRECTV SERVICE','DTV*DIRECTV
> SERVICER39')
> and TranCode not in (7008,7023)
> Group By CustomerKey,transdate
> order by transdate
>
> TIA, ChrisR
>|||Theres table scans either way. But the order by query has
sorts as well.
>--Original Message--
>Did you view the query plan with and without the order
by? What did you
>see?
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"ChrisR" <anonymous@.discussions.microsoft.com> wrote in
message
>news:21a7001c45afd$08f70a50$a501280a@.phx.gbl...
>> sql2k sp3
>> This query takes 40 seconds to run in Query Analyzer.
If I
>> run it without the Order By it takes 0 seconds. This in
>> itself isnt to bizarre. But heres the catch, the
Clustered
>> Index is on the transdate coulmn. That being the case,
>> shouldnt the difference be less?
>>
>> select top 100 CustomerKey,transdate
>> from transdtl
>> where MerchName in ('DTV*DIRECTV SERVICE','DTV*DIRECTV
>> SERVICER39')
>> and TranCode not in (7008,7023)
>> Group By CustomerKey,transdate
>> order by transdate
>>
>> TIA, ChrisR
>
>.
>|||try
Group By transdate, CustomerKey
I'm guessing that without the order by it just has to get the first 100 groups. With the order by it has to get all the groups to sort.
Have a look at the number of rows in steps in the query plan.
"ChrisR" wrote:
> sql2k sp3
> This query takes 40 seconds to run in Query Analyzer. If I
> run it without the Order By it takes 0 seconds. This in
> itself isnt to bizarre. But heres the catch, the Clustered
> Index is on the transdate coulmn. That being the case,
> shouldnt the difference be less?
>
> select top 100 CustomerKey,transdate
> from transdtl
> where MerchName in ('DTV*DIRECTV SERVICE','DTV*DIRECTV
> SERVICER39')
> and TranCode not in (7008,7023)
> Group By CustomerKey,transdate
> order by transdate
>
> TIA, ChrisR
>|||Even just using Order By TramsDate, it still slows it down just the same.
"Nigel Rivett" <NigelRivett@.discussions.microsoft.com> wrote in message
news:1BB2D0D3-8C0D-4024-98C7-317014533102@.microsoft.com...
> try
> Group By transdate, CustomerKey
>
> I'm guessing that without the order by it just has to get the first 100
groups. With the order by it has to get all the groups to sort.
> Have a look at the number of rows in steps in the query plan.
>
> "ChrisR" wrote:
> > sql2k sp3
> >
> > This query takes 40 seconds to run in Query Analyzer. If I
> > run it without the Order By it takes 0 seconds. This in
> > itself isnt to bizarre. But heres the catch, the Clustered
> > Index is on the transdate coulmn. That being the case,
> > shouldnt the difference be less?
> >
> >
> > select top 100 CustomerKey,transdate
> > from transdtl
> > where MerchName in ('DTV*DIRECTV SERVICE','DTV*DIRECTV
> > SERVICER39')
> > and TranCode not in (7008,7023)
> > Group By CustomerKey,transdate
> > order by transdate
> >
> >
> >
> > TIA, ChrisR
> >
> >|||Chris,
The query is asking for different results depending on whether you
include the ORDER BY. Without the ORDER BY, you are asking for any 100
rows from among the (Customer, transdate) pairs appearing in rows that
satisfy your MerchName and TranCode criteria. With the ORDER BY, you
are asking for a specific 100 (Customer, transdate) pairs satisfying
your MerchName and TranCode criteria - the 100 pairs that have the
earliest transdate values.
There are various scenarios where the query will be slower with an
ORDER BY clause, although I'm not sure why you are seeing a table scan
(i.e., a clustered index scan) in both situations. In addition, "Table
Scan" should not appear as an operator when the table has a clustered
index, so do you think you could post the CREATE TABLE and CREATE INDEX
statements for these tables and the estimated execution plans obtained
with the SET SHOWPLAN_TEXT ON?
Steve Kass
Drew University
ChrisR wrote:
>sql2k sp3
>This query takes 40 seconds to run in Query Analyzer. If I
>run it without the Order By it takes 0 seconds. This in
>itself isnt to bizarre. But heres the catch, the Clustered
>Index is on the transdate coulmn. That being the case,
>shouldnt the difference be less?
>
>select top 100 CustomerKey,transdate
>from transdtl
>where MerchName in ('DTV*DIRECTV SERVICE','DTV*DIRECTV
>SERVICER39')
>and TranCode not in (7008,7023)
>Group By CustomerKey,transdate
>order by transdate
>
>TIA, ChrisR
>
>|||Yes but Ill have to repost Monday. Have a great weekend.
"Steve Kass" <skass@.drew.edu> wrote in message
news:eWYRFh5WEHA.2972@.TK2MSFTNGP12.phx.gbl...
> Chris,
> The query is asking for different results depending on whether you
> include the ORDER BY. Without the ORDER BY, you are asking for any 100
> rows from among the (Customer, transdate) pairs appearing in rows that
> satisfy your MerchName and TranCode criteria. With the ORDER BY, you
> are asking for a specific 100 (Customer, transdate) pairs satisfying
> your MerchName and TranCode criteria - the 100 pairs that have the
> earliest transdate values.
> There are various scenarios where the query will be slower with an
> ORDER BY clause, although I'm not sure why you are seeing a table scan
> (i.e., a clustered index scan) in both situations. In addition, "Table
> Scan" should not appear as an operator when the table has a clustered
> index, so do you think you could post the CREATE TABLE and CREATE INDEX
> statements for these tables and the estimated execution plans obtained
> with the SET SHOWPLAN_TEXT ON?
> Steve Kass
> Drew University
> ChrisR wrote:
> >sql2k sp3
> >
> >This query takes 40 seconds to run in Query Analyzer. If I
> >run it without the Order By it takes 0 seconds. This in
> >itself isnt to bizarre. But heres the catch, the Clustered
> >Index is on the transdate coulmn. That being the case,
> >shouldnt the difference be less?
> >
> >
> >select top 100 CustomerKey,transdate
> >from transdtl
> >where MerchName in ('DTV*DIRECTV SERVICE','DTV*DIRECTV
> >SERVICER39')
> >and TranCode not in (7008,7023)
> >Group By CustomerKey,transdate
> >order by transdate
> >
> >
> >
> >TIA, ChrisR
> >
> >
> >
>

order by slowing me down X 40

sql2k sp3
This query takes 40 seconds to run in Query Analyzer. If I
run it without the Order By it takes 0 seconds. This in
itself isnt to bizarre. But heres the catch, the Clustered
Index is on the transdate coulmn. That being the case,
shouldnt the difference be less?
select top 100 CustomerKey,transdate
from transdtl
where MerchName in ('DTV*DIRECTV SERVICE','DTV*DIRECTV
SERVICER39')
and TranCode not in (7008,7023)
Group By CustomerKey,transdate
order by transdate
TIA, ChrisRDid you view the query plan with and without the order by? What did you
see?
http://www.aspfaq.com/
(Reverse address to reply.)
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:21a7001c45afd$08f70a50$a501280a@.phx
.gbl...
> sql2k sp3
> This query takes 40 seconds to run in Query Analyzer. If I
> run it without the Order By it takes 0 seconds. This in
> itself isnt to bizarre. But heres the catch, the Clustered
> Index is on the transdate coulmn. That being the case,
> shouldnt the difference be less?
>
> select top 100 CustomerKey,transdate
> from transdtl
> where MerchName in ('DTV*DIRECTV SERVICE','DTV*DIRECTV
> SERVICER39')
> and TranCode not in (7008,7023)
> Group By CustomerKey,transdate
> order by transdate
>
> TIA, ChrisR
>|||Theres table scans either way. But the order by query has
sorts as well.

>--Original Message--
>Did you view the query plan with and without the order
by? What did you
>see?
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"ChrisR" <anonymous@.discussions.microsoft.com> wrote in
message
> news:21a7001c45afd$08f70a50$a501280a@.phx
.gbl...
If I[vbcol=seagreen]
Clustered[vbcol=seagreen]
>
>.
>|||try
Group By transdate, CustomerKey
I'm guessing that without the order by it just has to get the first 100 grou
ps. With the order by it has to get all the groups to sort.
Have a look at the number of rows in steps in the query plan.
"ChrisR" wrote:

> sql2k sp3
> This query takes 40 seconds to run in Query Analyzer. If I
> run it without the Order By it takes 0 seconds. This in
> itself isnt to bizarre. But heres the catch, the Clustered
> Index is on the transdate coulmn. That being the case,
> shouldnt the difference be less?
>
> select top 100 CustomerKey,transdate
> from transdtl
> where MerchName in ('DTV*DIRECTV SERVICE','DTV*DIRECTV
> SERVICER39')
> and TranCode not in (7008,7023)
> Group By CustomerKey,transdate
> order by transdate
>
> TIA, ChrisR
>|||Even just using Order By TramsDate, it still slows it down just the same.
"Nigel Rivett" <NigelRivett@.discussions.microsoft.com> wrote in message
news:1BB2D0D3-8C0D-4024-98C7-317014533102@.microsoft.com...
> try
> Group By transdate, CustomerKey
>
> I'm guessing that without the order by it just has to get the first 100
groups. With the order by it has to get all the groups to sort.[vbcol=seagreen]
> Have a look at the number of rows in steps in the query plan.
>
> "ChrisR" wrote:
>|||Chris,
The query is asking for different results depending on whether you
include the ORDER BY. Without the ORDER BY, you are asking for any 100
rows from among the (Customer, transdate) pairs appearing in rows that
satisfy your MerchName and TranCode criteria. With the ORDER BY, you
are asking for a specific 100 (Customer, transdate) pairs satisfying
your MerchName and TranCode criteria - the 100 pairs that have the
earliest transdate values.
There are various scenarios where the query will be slower with an
ORDER BY clause, although I'm not sure why you are seeing a table scan
(i.e., a clustered index scan) in both situations. In addition, "Table
Scan" should not appear as an operator when the table has a clustered
index, so do you think you could post the CREATE TABLE and CREATE INDEX
statements for these tables and the estimated execution plans obtained
with the SET SHOWPLAN_TEXT ON?
Steve Kass
Drew University
ChrisR wrote:

>sql2k sp3
>This query takes 40 seconds to run in Query Analyzer. If I
>run it without the Order By it takes 0 seconds. This in
>itself isnt to bizarre. But heres the catch, the Clustered
>Index is on the transdate coulmn. That being the case,
>shouldnt the difference be less?
>
>select top 100 CustomerKey,transdate
>from transdtl
>where MerchName in ('DTV*DIRECTV SERVICE','DTV*DIRECTV
>SERVICER39')
>and TranCode not in (7008,7023)
>Group By CustomerKey,transdate
>order by transdate
>
>TIA, ChrisR
>
>|||Yes but Ill have to repost Monday. Have a great weekend.
"Steve Kass" <skass@.drew.edu> wrote in message
news:eWYRFh5WEHA.2972@.TK2MSFTNGP12.phx.gbl...
> Chris,
> The query is asking for different results depending on whether you
> include the ORDER BY. Without the ORDER BY, you are asking for any 100
> rows from among the (Customer, transdate) pairs appearing in rows that
> satisfy your MerchName and TranCode criteria. With the ORDER BY, you
> are asking for a specific 100 (Customer, transdate) pairs satisfying
> your MerchName and TranCode criteria - the 100 pairs that have the
> earliest transdate values.
> There are various scenarios where the query will be slower with an
> ORDER BY clause, although I'm not sure why you are seeing a table scan
> (i.e., a clustered index scan) in both situations. In addition, "Table
> Scan" should not appear as an operator when the table has a clustered
> index, so do you think you could post the CREATE TABLE and CREATE INDEX
> statements for these tables and the estimated execution plans obtained
> with the SET SHOWPLAN_TEXT ON?
> Steve Kass
> Drew University
> ChrisR wrote:
>
>sql

order by slowing me down

sql2k sp3
This query takes 14 seconds to run in Query Analyzer. If I
run it without the Order By it takes 0 seconds. This in
itself isnt to bizarre. But heres the catch, the Clustered
Index is on the transdate coulmn. That being the case,
shouldnt the difference be less? Some fun facts:
I didnt design this table. I know it has too many NULLs.
Something I noticed in the output of showplan_text is that
its sorting by customerkey.
Below is the DDL, query, and output from set
showplan_text on:
CREATE TABLE [dbo].[TransDtl0] (
[TransDtlKey] [int] NOT NULL ,
[CustomerKey] [int] NULL ,
[SerialNbr] [char] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[TranCode] [char] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[TransDate] [smalldatetime] NULL ,
[TransDateShort] [char] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[TransDateMonth] [tinyint] NULL ,
[TransDateYear] [smallint] NULL ,
[TransAmt] [money] NULL ,
[RefNbr] [char] (23) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[MerchName] [varchar] (25) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [varchar] (15) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[State] [varchar] (3) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[RejectReason] [varchar] (15) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PostDate] [datetime] NULL ,
[PostDateShort] [char] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PostDateMonth] [tinyint] NULL ,
[PostDateYear] [smallint] NULL ,
[CreateDate] [datetime] NULL ,
[MerchSIC] [char] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [IX_TransDtl0_TransDate] ON
[dbo].[TransDtl0]([TransDate]) WITH FILLFACTOR = 100 ON
[PRIMARY]
GO
CREATE INDEX [IX_TransDtl0_PostDate] ON [dbo].[TransDtl0]
([PostDate]) WITH FILLFACTOR = 100 ON [PRIMARY]
GO
CREATE INDEX [IX_TransDtl0_CustomerKey] ON [dbo].
[TransDtl0]([CustomerKey]) WITH FILLFACTOR = 100 ON
[PRIMARY]
GO
CREATE INDEX [IX_TransDtl0_RefNo] ON [dbo].[TransDtl0]
([RefNbr]) WITH FILLFACTOR = 100 ON [PRIMARY]
GO
CREATE INDEX [IX_TransDtl0_SerialNbr] ON [dbo].
[TransDtl0]([SerialNbr]) WITH FILLFACTOR = 100 ON
[PRIMARY]
GO
/****** The index created by the following statement is
for internal use only. ******/
/****** It is not a real index but exists as statistics
only. ******/
if (@.@.microsoftversion > 0x07000000 )
EXEC ('CREATE STATISTICS [Statistic_MerchSIC] ON [dbo].
[TransDtl0] ([MerchSIC]) ')
GO
CREATE INDEX [IX_TransDtl0] ON [dbo].[TransDtl0]
([MerchName]) ON [PRIMARY]
GO
select
CustomerKey
from transdtl0 t
where MerchName in ('DTV*DIRECTV SERVICE','DTV*DIRECTV
SERVICER39')
and TranCode <> 7008 and trancode <> 7023
Group By t.CustomerKey
order by min(t.transdate)
StmtText
---
---
---
---
--
select
CustomerKey
from transdtl0 t
where MerchName in ('DTV*DIRECTV SERVICE','DTV*DIRECTV
SERVICER39')
and TranCode <> 7008 and trancode <> 7023
Group By t.CustomerKey
order by
min(t.transdate)
--t.customerkey,
--truncate table tmpdtl
(1 row(s) affected)
StmtText
---
---
---
---
----
|--Parallelism(Gather Streams, ORDER BY:([Expr1001] ASC))
|--Sort(ORDER BY:([Expr1001] ASC))
|--Stream Aggregate(GROUP BY:([t].
[CustomerKey]) DEFINE:([Expr1001]=MIN([t].[TransDate])))
|--Sort(ORDER BY:([t].[CustomerKey] ASC))
|--Parallelism(Repartition Streams,
PARTITION COLUMNS:([t].[CustomerKey]))
|--Clustered Index Scan(OBJECT:
([MatViewTest].[dbo].[TransDtl0].[IX_TransDtl0_TransDate]
AS [t]), WHERE:((([t].[MerchName]='DTV*DIRECTV SERVICER39'
OR [t].[MerchName]='DTV*DIRECTV SERVICE') AND Convert([t].
[TranCode])<>7008) AND Convert([t].[TranCode])<>7023))
(6 row(s) affected)
TIA, ChrisRI just found some stuff out. As a result, this question
doesnt matter. Im going to repost a similar question
shortly. Hopefully nobody has spent time on this. Sorry.
>--Original Message--
>sql2k sp3
>This query takes 14 seconds to run in Query Analyzer. If
I
>run it without the Order By it takes 0 seconds. This in
>itself isnt to bizarre. But heres the catch, the
Clustered
>Index is on the transdate coulmn. That being the case,
>shouldnt the difference be less? Some fun facts:
>I didnt design this table. I know it has too many NULLs.
>Something I noticed in the output of showplan_text is
that
>its sorting by customerkey.
>Below is the DDL, query, and output from set
>showplan_text on:
>CREATE TABLE [dbo].[TransDtl0] (
> [TransDtlKey] [int] NOT NULL ,
> [CustomerKey] [int] NULL ,
> [SerialNbr] [char] (10) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
> [TranCode] [char] (4) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
> [TransDate] [smalldatetime] NULL ,
> [TransDateShort] [char] (10) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
> [TransDateMonth] [tinyint] NULL ,
> [TransDateYear] [smallint] NULL ,
> [TransAmt] [money] NULL ,
> [RefNbr] [char] (23) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
> [MerchName] [varchar] (25) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
> [City] [varchar] (15) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
> [State] [varchar] (3) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
> [RejectReason] [varchar] (15) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
> [PostDate] [datetime] NULL ,
> [PostDateShort] [char] (10) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
> [PostDateMonth] [tinyint] NULL ,
> [PostDateYear] [smallint] NULL ,
> [CreateDate] [datetime] NULL ,
> [MerchSIC] [char] (4) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL
>) ON [PRIMARY]
>GO
> CREATE CLUSTERED INDEX [IX_TransDtl0_TransDate] ON
>[dbo].[TransDtl0]([TransDate]) WITH FILLFACTOR = 100 ON
>[PRIMARY]
>GO
> CREATE INDEX [IX_TransDtl0_PostDate] ON [dbo].
[TransDtl0]
>([PostDate]) WITH FILLFACTOR = 100 ON [PRIMARY]
>GO
> CREATE INDEX [IX_TransDtl0_CustomerKey] ON [dbo].
>[TransDtl0]([CustomerKey]) WITH FILLFACTOR = 100 ON
>[PRIMARY]
>GO
> CREATE INDEX [IX_TransDtl0_RefNo] ON [dbo].[TransDtl0]
>([RefNbr]) WITH FILLFACTOR = 100 ON [PRIMARY]
>GO
> CREATE INDEX [IX_TransDtl0_SerialNbr] ON [dbo].
>[TransDtl0]([SerialNbr]) WITH FILLFACTOR = 100 ON
>[PRIMARY]
>GO
>/****** The index created by the following statement is
>for internal use only. ******/
>/****** It is not a real index but exists as statistics
>only. ******/
>if (@.@.microsoftversion > 0x07000000 )
>EXEC ('CREATE STATISTICS [Statistic_MerchSIC] ON [dbo].
>[TransDtl0] ([MerchSIC]) ')
>GO
> CREATE INDEX [IX_TransDtl0] ON [dbo].[TransDtl0]
>([MerchName]) ON [PRIMARY]
>GO
>
>select
>CustomerKey
>from transdtl0 t
>where MerchName in ('DTV*DIRECTV SERVICE','DTV*DIRECTV
>SERVICER39')
>and TranCode <> 7008 and trancode <> 7023
>Group By t.CustomerKey
>order by min(t.transdate)
>
>StmtText
>
>
>
>----
-
>----
-
>----
-
>----
-
>--
>select
>CustomerKey
>from transdtl0 t
>where MerchName in ('DTV*DIRECTV SERVICE','DTV*DIRECTV
>SERVICER39')
>and TranCode <> 7008 and trancode <> 7023
>Group By t.CustomerKey
>order by
>min(t.transdate)
>--t.customerkey,
>--truncate table tmpdtl
>(1 row(s) affected)
>StmtText
>
>
>
>----
-
>----
-
>----
-
>----
-
>----
> |--Parallelism(Gather Streams, ORDER BY:([Expr1001]
ASC))
> |--Sort(ORDER BY:([Expr1001] ASC))
> |--Stream Aggregate(GROUP BY:([t].
>[CustomerKey]) DEFINE:([Expr1001]=MIN([t].[TransDate])))
> |--Sort(ORDER BY:([t].[CustomerKey] ASC))
> |--Parallelism(Repartition Streams,
>PARTITION COLUMNS:([t].[CustomerKey]))
> |--Clustered Index Scan(OBJECT:
>([MatViewTest].[dbo].[TransDtl0].[IX_TransDtl0_TransDate]
>AS [t]), WHERE:((([t].[MerchName]='DTV*DIRECTV
SERVICER39'
>OR [t].[MerchName]='DTV*DIRECTV SERVICE') AND Convert([t].
>[TranCode])<>7008) AND Convert([t].[TranCode])<>7023))
>(6 row(s) affected)
>
>TIA, ChrisR
>.
>

ORDER BY returns opposite sequence between SQL 2000 and 2005

I have the following query returning results in opposite order between
versions 2000 and 2005:-
SELECT * FROM tblname where form= 'L'
order by acolumn desc
The database has been migrated from 2000 to 2005, so the structure and data
should be identical. Here's the DDL for the table in SQL2000:-
USE [MSD_Contracts]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblname](
[Form] [varchar](1) NOT NULL,
[acolumn] [varchar](1) NOT NULL,
[FieldNo] [int] NOT NULL,
[dbField] [varchar](30) NOT NULL,
[Validation] [varchar](20) NOT NULL,
[AllowNulls] [varchar](1) NOT NULL,
CONSTRAINT [PK_tblname_1_12] PRIMARY KEY CLUSTERED
(
[FieldNo] ASC,
[Form] ASC,
[acolumn] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON
[PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
Any ideas please?Hi Adrian
The order should be the case assuming that the collations are the same. Do
you have some sample data? http://www.aspfaq.com/etiquette.asp?id=5006
You should change the varchar(1) columns.
John
"Adrian" wrote:
> I have the following query returning results in opposite order between
> versions 2000 and 2005:-
> SELECT * FROM tblname where form= 'L'
> order by acolumn desc
> The database has been migrated from 2000 to 2005, so the structure and data
> should be identical. Here's the DDL for the table in SQL2000:-
> USE [MSD_Contracts]
> GO
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_PADDING ON
> GO
> CREATE TABLE [dbo].[tblname](
> [Form] [varchar](1) NOT NULL,
> [acolumn] [varchar](1) NOT NULL,
> [FieldNo] [int] NOT NULL,
> [dbField] [varchar](30) NOT NULL,
> [Validation] [varchar](20) NOT NULL,
> [AllowNulls] [varchar](1) NOT NULL,
> CONSTRAINT [PK_tblname_1_12] PRIMARY KEY CLUSTERED
> (
> [FieldNo] ASC,
> [Form] ASC,
> [acolumn] ASC
> )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY => OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON
> [PRIMARY]
> ) ON [PRIMARY]
> GO
> SET ANSI_PADDING OFF
> Any ideas please?|||Collation sequence is the same in both versions -
SQL_Latin1_General_CP1_CI_AS. Here is some sample data:_
Form FrontBack FieldNo dbField Validation
AllowNulls
-- -- -- --
-- --
L F 470 SignaturePresent AlphaNum
N
L F 210 DependentQ Bit
N
L F 200 DependentN Bit
N
L F 190 DependentY Bit
N
L F 180 MarriageCertQ Bit
N
L F 170 MarriageCertN Bit
N
L F 160 MarriageCertY Bit
N
L F 150 ConsentQ Bit
N
L F 140 ConsentN Bit
N
L F 130 ConsentY Bit
N
L F 120 Under18Years Bit
N
L F 110 CAltered Bit
N
L F 100 SignedAndDatedQ Bit
N
L F 90 SignedAndDatedN Bit
N
L F 80 SignedAndDatedY Bit
N
L F 70 NameAndDOBQ Bit
N
L F 60 NameAndDOBN Bit
N
L F 50 NameAndDOBY Bit
N
L F 40 Barcode Num
N
L F 30 FormType1 AlphaNum
N
L F 20 ImageRef1 AlphaNum
N
L F 10 BatchNo Num
N
L B 420 EvidenceSent Bit
N
L B 410 EvidenceToWinz Bit
N
L B 400 ExtraEvidence Bit
N
L B 390 Oaltered Bit
N
L B 380 BankQ Bit
N
L B 370 BankN Bit
N
L B 360 BankY Bit
N
L B 340 IRDQ Bit
N
L B 330 IRDN Bit
N
L B 320 IRDY Bit
N
L B 310 ResidentQ Bit
N
L B 300 ResidentN Bit
N
L B 290 ResidentY Bit
N
L B 280 CitizenQ Bit
N
L B 270 CitizenN Bit
N
L B 260 CitizenY Bit
N
L B 250 Barcode Num
N
L B 240 FormType2 AlphaNum
N
L B 230 ImageRef2 AlphaNum
N
L B 220 BatchNo Num
N
(42 row(s) affected)
"John Bell" wrote:
> Hi Adrian
> The order should be the case assuming that the collations are the same. Do
> you have some sample data? http://www.aspfaq.com/etiquette.asp?id=5006
> You should change the varchar(1) columns.
> John
> "Adrian" wrote:
> > I have the following query returning results in opposite order between
> > versions 2000 and 2005:-
> > SELECT * FROM tblname where form= 'L'
> > order by acolumn desc
> >
> > The database has been migrated from 2000 to 2005, so the structure and data
> > should be identical. Here's the DDL for the table in SQL2000:-
> > USE [MSD_Contracts]
> > GO
> > SET ANSI_NULLS ON
> > GO
> > SET QUOTED_IDENTIFIER ON
> > GO
> > SET ANSI_PADDING ON
> > GO
> > CREATE TABLE [dbo].[tblname](
> > [Form] [varchar](1) NOT NULL,
> > [acolumn] [varchar](1) NOT NULL,
> > [FieldNo] [int] NOT NULL,
> > [dbField] [varchar](30) NOT NULL,
> > [Validation] [varchar](20) NOT NULL,
> > [AllowNulls] [varchar](1) NOT NULL,
> > CONSTRAINT [PK_tblname_1_12] PRIMARY KEY CLUSTERED
> > (
> > [FieldNo] ASC,
> > [Form] ASC,
> > [acolumn] ASC
> > )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY => > OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON
> > [PRIMARY]
> > ) ON [PRIMARY]
> >
> > GO
> > SET ANSI_PADDING OFF
> >
> > Any ideas please?|||Hi Arian
You did not read the link I posted about sample data.
Here is some code that would have been useful:
USE TEMPDB
GO
CREATE TABLE [dbo].[tblname](
[Form] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[FrontBack] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[FieldNo] [int] NOT NULL ,
[dbField] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Validation] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AllowNulls] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_tblname_1_12] PRIMARY KEY CLUSTERED
(
[FieldNo] ASC,
[Form] ASC,
[FrontBack] ASC
)
)
INSERT INTO tblname (Form, FrontBack, FieldNo, dbField, Validation,
AllowNulls)
SELECT 'L','F',470,'SignaturePresent','AlphaNum','N'
UNION ALL SELECT 'L','F',210,'DependentQ','Bit','N'
UNION ALL SELECT 'L','F',200,'DependentN','Bit','N'
UNION ALL SELECT 'L','F',190,'DependentY','Bit','N'
UNION ALL SELECT 'L','F',180,'MarriageCertQ','Bit','N'
UNION ALL SELECT 'L','F',170,'MarriageCertN','Bit','N'
UNION ALL SELECT 'L','F',160,'MarriageCertY','Bit','N'
UNION ALL SELECT 'L','F',150,'ConsentQ','Bit','N'
UNION ALL SELECT 'L','F',140,'ConsentN','Bit','N'
UNION ALL SELECT 'L','F',130,'ConsentY','Bit','N'
UNION ALL SELECT 'L','F',120,'Under18Years','Bit','N'
UNION ALL SELECT 'L','F',110,'CAltered','Bit','N'
UNION ALL SELECT 'L','F',100,'SignedAndDatedQ','Bit','N'
UNION ALL SELECT 'L','F',90,'SignedAndDatedN','Bit','N'
UNION ALL SELECT 'L','F',80,'SignedAndDatedY','Bit','N'
UNION ALL SELECT 'L','F',70,'NameAndDOBQ','Bit','N'
UNION ALL SELECT 'L','F',60,'NameAndDOBN','Bit','N'
UNION ALL SELECT 'L','F',50,'NameAndDOBY','Bit','N'
UNION ALL SELECT 'L','F',40,'Barcode','Num','N'
UNION ALL SELECT 'L','F',30,'FormType1','AlphaNum','N'
UNION ALL SELECT 'L','F',20,'ImageRef1','AlphaNum','N'
UNION ALL SELECT 'L','F',10,'BatchNo','Num','N'
UNION ALL SELECT 'L','B',420,'EvidenceSent','Bit','N'
UNION ALL SELECT 'L','B',410,'EvidenceToWinz','Bit','N'
UNION ALL SELECT 'L','B',400,'ExtraEvidence','Bit','N'
UNION ALL SELECT 'L','B',390,'Oaltered','Bit','N'
UNION ALL SELECT 'L','B',380,'BankQ','Bit','N'
UNION ALL SELECT 'L','B',370,'BankN','Bit','N'
UNION ALL SELECT 'L','B',360,'BankY','Bit','N'
UNION ALL SELECT 'L','B',340,'IRDQ','Bit','N'
UNION ALL SELECT 'L','B',330,'IRDN','Bit','N'
UNION ALL SELECT 'L','B',320,'IRDY','Bit','N'
UNION ALL SELECT 'L','B',310,'ResidentQ','Bit','N'
UNION ALL SELECT 'L','B',300,'ResidentN','Bit','N'
UNION ALL SELECT 'L','B',290,'ResidentY','Bit','N'
UNION ALL SELECT 'L','B',280,'CitizenQ','Bit','N'
UNION ALL SELECT 'L','B',270,'CitizenN','Bit','N'
UNION ALL SELECT 'L','B',260,'CitizenY','Bit','N'
UNION ALL SELECT 'L','B',250,'Barcode','Num','N'
UNION ALL SELECT 'L','B',240,'FormType2','AlphaNum','N'
UNION ALL SELECT 'L','B',230,'ImageRef2','AlphaNum','N'
UNION ALL SELECT 'L','B',220,'BatchNo','Num','N'
SELECT *
FROM tblname
where form= 'L'
order by FrontBack desc
The output I get from SQL 2000 is:
Form FrontBack FieldNo dbField Validation
AllowNulls
-- -- -- --
-- --
L F 10 BatchNo Num
N
L F 20 ImageRef1 AlphaNum
N
L F 30 FormType1 AlphaNum
N
L F 40 Barcode Num
N
L F 50 NameAndDOBY Bit
N
L F 60 NameAndDOBN Bit
N
L F 70 NameAndDOBQ Bit
N
L F 80 SignedAndDatedY Bit
N
L F 90 SignedAndDatedN Bit
N
L F 100 SignedAndDatedQ Bit
N
L F 110 CAltered Bit
N
L F 120 Under18Years Bit
N
L F 130 ConsentY Bit
N
L F 140 ConsentN Bit
N
L F 150 ConsentQ Bit
N
L F 160 MarriageCertY Bit
N
L F 170 MarriageCertN Bit
N
L F 180 MarriageCertQ Bit
N
L F 190 DependentY Bit
N
L F 200 DependentN Bit
N
L F 210 DependentQ Bit
N
L F 470 SignaturePresent AlphaNum
N
L B 220 BatchNo Num
N
L B 230 ImageRef2 AlphaNum
N
L B 240 FormType2 AlphaNum
N
L B 250 Barcode Num
N
L B 260 CitizenY Bit
N
L B 270 CitizenN Bit
N
L B 280 CitizenQ Bit
N
L B 290 ResidentY Bit
N
L B 300 ResidentN Bit
N
L B 310 ResidentQ Bit
N
L B 320 IRDY Bit
N
L B 330 IRDN Bit
N
L B 340 IRDQ Bit
N
L B 360 BankY Bit
N
L B 370 BankN Bit
N
L B 380 BankQ Bit
N
L B 390 Oaltered Bit
N
L B 400 ExtraEvidence Bit
N
L B 410 EvidenceToWinz Bit
N
L B 420 EvidenceSent Bit
N
(42 row(s) affected)
The output I get from SQL 2005 is:
Form FrontBack FieldNo dbField Validation
AllowNulls
-- -- -- --
-- --
L F 10 BatchNo Num
N
L F 20 ImageRef1 AlphaNum
N
L F 30 FormType1 AlphaNum
N
L F 40 Barcode Num
N
L F 50 NameAndDOBY Bit
N
L F 60 NameAndDOBN Bit
N
L F 70 NameAndDOBQ Bit
N
L F 80 SignedAndDatedY Bit
N
L F 90 SignedAndDatedN Bit
N
L F 100 SignedAndDatedQ Bit
N
L F 110 CAltered Bit
N
L F 120 Under18Years Bit
N
L F 130 ConsentY Bit
N
L F 140 ConsentN Bit
N
L F 150 ConsentQ Bit
N
L F 160 MarriageCertY Bit
N
L F 170 MarriageCertN Bit
N
L F 180 MarriageCertQ Bit
N
L F 190 DependentY Bit
N
L F 200 DependentN Bit
N
L F 210 DependentQ Bit
N
L F 470 SignaturePresent AlphaNum
N
L B 220 BatchNo Num
N
L B 230 ImageRef2 AlphaNum
N
L B 240 FormType2 AlphaNum
N
L B 250 Barcode Num
N
L B 260 CitizenY Bit
N
L B 270 CitizenN Bit
N
L B 280 CitizenQ Bit
N
L B 290 ResidentY Bit
N
L B 300 ResidentN Bit
N
L B 310 ResidentQ Bit
N
L B 320 IRDY Bit
N
L B 330 IRDN Bit
N
L B 340 IRDQ Bit
N
L B 360 BankY Bit
N
L B 370 BankN Bit
N
L B 380 BankQ Bit
N
L B 390 Oaltered Bit
N
L B 400 ExtraEvidence Bit
N
L B 410 EvidenceToWinz Bit
N
L B 420 EvidenceSent Bit
N
(42 row(s) affected)
The order by only guarantees that FrontBack will be ordered 'F' then 'B' and
this is the case on both instances. As FieldNo and Form are not in the order
by their order is not guaranteed.
John
"Adrian" wrote:
> Collation sequence is the same in both versions -
> SQL_Latin1_General_CP1_CI_AS. Here is some sample data:_
> Form FrontBack FieldNo dbField Validation
> AllowNulls
> -- -- -- --
> -- --
> L F 470 SignaturePresent AlphaNum
> N
> L F 210 DependentQ Bit
> N
> L F 200 DependentN Bit
> N
> L F 190 DependentY Bit
> N
> L F 180 MarriageCertQ Bit
> N
> L F 170 MarriageCertN Bit
> N
> L F 160 MarriageCertY Bit
> N
> L F 150 ConsentQ Bit
> N
> L F 140 ConsentN Bit
> N
> L F 130 ConsentY Bit
> N
> L F 120 Under18Years Bit
> N
> L F 110 CAltered Bit
> N
> L F 100 SignedAndDatedQ Bit
> N
> L F 90 SignedAndDatedN Bit
> N
> L F 80 SignedAndDatedY Bit
> N
> L F 70 NameAndDOBQ Bit
> N
> L F 60 NameAndDOBN Bit
> N
> L F 50 NameAndDOBY Bit
> N
> L F 40 Barcode Num
> N
> L F 30 FormType1 AlphaNum
> N
> L F 20 ImageRef1 AlphaNum
> N
> L F 10 BatchNo Num
> N
> L B 420 EvidenceSent Bit
> N
> L B 410 EvidenceToWinz Bit
> N
> L B 400 ExtraEvidence Bit
> N
> L B 390 Oaltered Bit
> N
> L B 380 BankQ Bit
> N
> L B 370 BankN Bit
> N
> L B 360 BankY Bit
> N
> L B 340 IRDQ Bit
> N
> L B 330 IRDN Bit
> N
> L B 320 IRDY Bit
> N
> L B 310 ResidentQ Bit
> N
> L B 300 ResidentN Bit
> N
> L B 290 ResidentY Bit
> N
> L B 280 CitizenQ Bit
> N
> L B 270 CitizenN Bit
> N
> L B 260 CitizenY Bit
> N
> L B 250 Barcode Num
> N
> L B 240 FormType2 AlphaNum
> N
> L B 230 ImageRef2 AlphaNum
> N
> L B 220 BatchNo Num
> N
> (42 row(s) affected)
>
> "John Bell" wrote:
> > Hi Adrian
> >
> > The order should be the case assuming that the collations are the same. Do
> > you have some sample data? http://www.aspfaq.com/etiquette.asp?id=5006
> >
> > You should change the varchar(1) columns.
> >
> > John
> >
> > "Adrian" wrote:
> >
> > > I have the following query returning results in opposite order between
> > > versions 2000 and 2005:-
> > > SELECT * FROM tblname where form= 'L'
> > > order by acolumn desc
> > >
> > > The database has been migrated from 2000 to 2005, so the structure and data
> > > should be identical. Here's the DDL for the table in SQL2000:-
> > > USE [MSD_Contracts]
> > > GO
> > > SET ANSI_NULLS ON
> > > GO
> > > SET QUOTED_IDENTIFIER ON
> > > GO
> > > SET ANSI_PADDING ON
> > > GO
> > > CREATE TABLE [dbo].[tblname](
> > > [Form] [varchar](1) NOT NULL,
> > > [acolumn] [varchar](1) NOT NULL,
> > > [FieldNo] [int] NOT NULL,
> > > [dbField] [varchar](30) NOT NULL,
> > > [Validation] [varchar](20) NOT NULL,
> > > [AllowNulls] [varchar](1) NOT NULL,
> > > CONSTRAINT [PK_tblname_1_12] PRIMARY KEY CLUSTERED
> > > (
> > > [FieldNo] ASC,
> > > [Form] ASC,
> > > [acolumn] ASC
> > > )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY => > > OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON
> > > [PRIMARY]
> > > ) ON [PRIMARY]
> > >
> > > GO
> > > SET ANSI_PADDING OFF
> > >
> > > Any ideas please?

Order by rank

Hello
I have this query which works fine, but I would like to order by rank, so i
get most "correct" results first.....
SELECT * FROM Questions
WHERE FREETEXT(Question, @.Keywords)
OR FREETEXT(Answer, @.Keywords)
OR FREETEXT(Headline, @.Keywords)
TIA
/Lasse
here's how I would tackle this:
declare declare @.keywords varchar(100)
set @.keywords='microsoft'
select rank= case when search1.rank>=search2.rank and
search1.rank>=search3.rank then search1.rank
when search2.rank>=search3.rank and search2.rank>=search1.rank then
search2.rank
when search3.rank>=search1.rank and search3.rank>=search1.rank then
search3.rank end
from questions join freetexttable(Questions, question, @.keywords) as Search1
on Search1.[key]=pk
join freetexttable(Questions, Answer, @.keywords) as Search2 on
Search2.[key]=pk
join freetexttable(Questions, HeadLine, @.keywords) as Search3 on
Search3.[key]=pk
order by rank
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:esNSb4WlEHA.1152@.TK2MSFTNGP11.phx.gbl...
> Hello
> I have this query which works fine, but I would like to order by rank, so
i
> get most "correct" results first.....
> SELECT * FROM Questions
> WHERE FREETEXT(Question, @.Keywords)
> OR FREETEXT(Answer, @.Keywords)
> OR FREETEXT(Headline, @.Keywords)
>
> TIA
> /Lasse
>
|||Lasse,
I don't want to assume anything here, but in order by rank, you would need
to use FREETEXTTABLE as FREETEXT does not provide that functionality. Again,
not wanting to assume anything, without having you provide more information,
but do you want a one column to be "ranked" higher than the over columns in
your tables?
More information on what you are trying to achieve would be helpful in order
to provide a more specific solution for you.
Thanks,
John
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:esNSb4WlEHA.1152@.TK2MSFTNGP11.phx.gbl...
> Hello
> I have this query which works fine, but I would like to order by rank, so
i
> get most "correct" results first.....
> SELECT * FROM Questions
> WHERE FREETEXT(Question, @.Keywords)
> OR FREETEXT(Answer, @.Keywords)
> OR FREETEXT(Headline, @.Keywords)
>
> TIA
> /Lasse
>
|||John,
no column should be ranked higher than other.
works fine with "rank" when one column, but not sure how to do it when there
are 3 columns.
"John Kane" <jt-kane@.comcast.net> wrote in message
news:%23IaKv3blEHA.1904@.TK2MSFTNGP09.phx.gbl...
> Lasse,
> I don't want to assume anything here, but in order by rank, you would need
> to use FREETEXTTABLE as FREETEXT does not provide that functionality.
Again,
> not wanting to assume anything, without having you provide more
information,
> but do you want a one column to be "ranked" higher than the over columns
in
> your tables?
> More information on what you are trying to achieve would be helpful in
order[vbcol=seagreen]
> to provide a more specific solution for you.
> Thanks,
> John
>
> "Lasse Edsvik" <lasse@.nospam.com> wrote in message
> news:esNSb4WlEHA.1152@.TK2MSFTNGP11.phx.gbl...
so
> i
>
|||If that's the case you should add them respective ranks, as illustrated
below.
declare @.keywords varchar(100)
set @.keywords='microsoft'
select question, answer, headline, rank=
search1.rank+search2.rank+search3.rank
from questions join freetexttable(Questions, question, @.keywords) as Search1
on Search1.[key]=pk
join freetexttable(Questions, Answer, @.keywords) as Search2 on
Search2.[key]=pk
join freetexttable(Questions, HeadLine, @.keywords) as Search3 on
Search3.[key]=pk
order by rank desc
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:%23IwqipklEHA.3876@.TK2MSFTNGP15.phx.gbl...
> John,
> no column should be ranked higher than other.
> works fine with "rank" when one column, but not sure how to do it when
there[vbcol=seagreen]
> are 3 columns.
>
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:%23IaKv3blEHA.1904@.TK2MSFTNGP09.phx.gbl...
need
> Again,
> information,
> in
> order
> so
>
|||Lasse,
Ok... and that's why I assume nothing and try to gather information first
before answering questions... <G>
You can use FREETEXTTABLE in the following multiple table & columns SQL FTS
query:
SELECT distinct e.OrderNo, e.Label
from ItemStock AS e, ItemTitles t, ItemHardware h,
containstable(ItemStock, Label, 'Billy') as A,
containstable(ItemTitles, Title, 'Stranger') as B,
containstable(ItemHardware, s_page, 'row') as C
where
A.[KEY] = e.OrderNo and -- OR = generates mutiple rows, and
therefore needs distinct e.OrderNo.
B.[KEY] = t.OrderNo and
C.[KEY] = h.OrderNo
Substitute ItemStock for your table Question, ItemTitles for your table
Answer and ItemHardware for your table Headline. Note, all the above tables
have Primary key - Foreign Key relationships as should your tables in order
for the joins to work correctly. You can also alter the above to have OR
conditions between the containstable (or freetexttable) clauses, but you
will need to use the distinct parameter to eliminate the duplicate rows. Let
me know if you need the DDL (create table, etc.) for the above tables as I
can email them to you if you want.
Thanks,
John
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:#IwqipklEHA.3876@.TK2MSFTNGP15.phx.gbl...
> John,
> no column should be ranked higher than other.
> works fine with "rank" when one column, but not sure how to do it when
there[vbcol=seagreen]
> are 3 columns.
>
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:%23IaKv3blEHA.1904@.TK2MSFTNGP09.phx.gbl...
need
> Again,
> information,
> in
> order
> so
>
|||What the heck?
In the original post the poster was using freetext where Question, Answer
and Headline would refer to columns in the table Questions.
Secondly the poster wanted to order by rank. I don't see anything in here
where you order by rank.
Are you possibly assuming something?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"John Kane" <jt-kane@.comcast.net> wrote in message
news:uiS6ymolEHA.2504@.TK2MSFTNGP14.phx.gbl...
> Lasse,
> Ok... and that's why I assume nothing and try to gather information first
> before answering questions... <G>
> You can use FREETEXTTABLE in the following multiple table & columns SQL
FTS
> query:
> SELECT distinct e.OrderNo, e.Label
> from ItemStock AS e, ItemTitles t, ItemHardware h,
> containstable(ItemStock, Label, 'Billy') as A,
> containstable(ItemTitles, Title, 'Stranger') as B,
> containstable(ItemHardware, s_page, 'row') as C
> where
> A.[KEY] = e.OrderNo and -- OR = generates mutiple rows, and
> therefore needs distinct e.OrderNo.
> B.[KEY] = t.OrderNo and
> C.[KEY] = h.OrderNo
>
> Substitute ItemStock for your table Question, ItemTitles for your table
> Answer and ItemHardware for your table Headline. Note, all the above
tables
> have Primary key - Foreign Key relationships as should your tables in
order
> for the joins to work correctly. You can also alter the above to have OR
> conditions between the containstable (or freetexttable) clauses, but you
> will need to use the distinct parameter to eliminate the duplicate rows.
Let[vbcol=seagreen]
> me know if you need the DDL (create table, etc.) for the above tables as I
> can email them to you if you want.
> Thanks,
> John
>
> "Lasse Edsvik" <lasse@.nospam.com> wrote in message
> news:#IwqipklEHA.3876@.TK2MSFTNGP15.phx.gbl...
> there
> need
columns[vbcol=seagreen]
rank,
>
|||In Lasse's reply to me, he stated that "no column should be ranked higher
than other.", so all that needs to be changed / added to my query example is
substituting freetexttable for containstable and adding an ORDER BY clause,
for example:
SELECT distinct t.OrderNo, t.Title, A.[RANK], B.[RANK], C.[RANK]
from ItemTitles AS t,
freetexttable(ItemTitles, Title, 'title') as A,
freetexttable(ItemTitles, Artist, 'microsoft') as B,
freetexttable(ItemTitles, Location, 'else') as C
where
A.[KEY] = t.OrderNo and
B.[KEY] = t.OrderNo and
C.[KEY] = t.OrderNo
ORDER BY A.[RANK], B.[RANK], C.[RANK] DESC
So, Hilary, I wasn't assuming anything, I did mis-read the initial question
(as you have as well from time-to-time) to indicate multiple tables, vs. a
single table with multiple columns and I've altered the above query to
correct this mistake. I was replying to Lasse's most recent post and I'll
wait for Lasse to reply with his feedback to this post as well. Please, feel
free to email me directly, if you have any questions &/or concerns.
A question for Lasse - Are you trying to get results from just one column or
across all three columns?
Best Regards,
John
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:Obq5shplEHA.3452@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> What the heck?
> In the original post the poster was using freetext where Question, Answer
> and Headline would refer to columns in the table Questions.
> Secondly the poster wanted to order by rank. I don't see anything in here
> where you order by rank.
> Are you possibly assuming something?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:uiS6ymolEHA.2504@.TK2MSFTNGP14.phx.gbl...
first[vbcol=seagreen]
> FTS
> tables
> order
> Let
I[vbcol=seagreen]
would[vbcol=seagreen]
functionality.[vbcol=seagreen]
> columns
in
> rank,
>
|||now, I'm even more confused. Lasse said ""no column should be ranked higher
than other.", but you are ranking them by a.rank, b.rank, c.rank desc.
So, it seems you are ranking a.rank higher than b.rank, and b.rank higher
than c.rank. Then you are sorting a.rank asc., b.rank asc, and c.rank desc.
This I really don't understand.
Surely you mean a.rank desc, b.rank desc, c.rank desc?
What am I missing?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"John Kane" <jt-kane@.comcast.net> wrote in message
news:%23hG8r9qlEHA.324@.TK2MSFTNGP11.phx.gbl...
> In Lasse's reply to me, he stated that "no column should be ranked higher
> than other.", so all that needs to be changed / added to my query example
is
> substituting freetexttable for containstable and adding an ORDER BY
clause,
> for example:
> SELECT distinct t.OrderNo, t.Title, A.[RANK], B.[RANK], C.[RANK]
> from ItemTitles AS t,
> freetexttable(ItemTitles, Title, 'title') as A,
> freetexttable(ItemTitles, Artist, 'microsoft') as B,
> freetexttable(ItemTitles, Location, 'else') as C
> where
> A.[KEY] = t.OrderNo and
> B.[KEY] = t.OrderNo and
> C.[KEY] = t.OrderNo
> ORDER BY A.[RANK], B.[RANK], C.[RANK] DESC
>
> So, Hilary, I wasn't assuming anything, I did mis-read the initial
question
> (as you have as well from time-to-time) to indicate multiple tables, vs. a
> single table with multiple columns and I've altered the above query to
> correct this mistake. I was replying to Lasse's most recent post and I'll
> wait for Lasse to reply with his feedback to this post as well. Please,
feel
> free to email me directly, if you have any questions &/or concerns.
> A question for Lasse - Are you trying to get results from just one column
or[vbcol=seagreen]
> across all three columns?
> Best Regards,
> John
>
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:Obq5shplEHA.3452@.TK2MSFTNGP15.phx.gbl...
Answer[vbcol=seagreen]
here[vbcol=seagreen]
> first
SQL[vbcol=seagreen]
table[vbcol=seagreen]
OR[vbcol=seagreen]
you[vbcol=seagreen]
rows.[vbcol=seagreen]
as[vbcol=seagreen]
> I
when[vbcol=seagreen]
> would
> functionality.
helpful
> in
>
|||Well, I just threw that in for you while waiting for Lasse's reply!
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:et9$kFtlEHA.704@.TK2MSFTNGP09.phx.gbl...
> now, I'm even more confused. Lasse said ""no column should be ranked
higher
> than other.", but you are ranking them by a.rank, b.rank, c.rank desc.
> So, it seems you are ranking a.rank higher than b.rank, and b.rank higher
> than c.rank. Then you are sorting a.rank asc., b.rank asc, and c.rank
desc.[vbcol=seagreen]
> This I really don't understand.
> Surely you mean a.rank desc, b.rank desc, c.rank desc?
> What am I missing?
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:%23hG8r9qlEHA.324@.TK2MSFTNGP11.phx.gbl...
higher[vbcol=seagreen]
example[vbcol=seagreen]
> is
> clause,
> question
a[vbcol=seagreen]
I'll[vbcol=seagreen]
> feel
column[vbcol=seagreen]
> or
> Answer
> here
> SQL
> table
in[vbcol=seagreen]
have[vbcol=seagreen]
> OR
> you
> rows.
tables[vbcol=seagreen]
> as
> when
> helpful
by
>

ORDER BY Question

I have a table which contains a sNAME field - a persons name of "firstame space
lastname".
I have a query that delivers a recordset using "ORDER BY sName"
Now, a client has asked to see the data sorted by "lastname". Is there any
method using an SQL expression to deliver that recordset sorted by lastname?
...or do I have to re-organize my data fields and data?
I was hoping for a quick 'sql function' solution instead of changing the
database.
Brian
Brian,
You could use functions to determine the lastname and sort by that but that
technique would probably preclude the use of NC indexes to resolve the query
and may negatively impact performance. Something like (or some variation
of):
SELECT <COLUMN LIST.
FROM <TABLE>
ORDER BY SUBSTRING(<COLUMN>, CHARINDEX(' ',<COLUMN>)+ 1,
DATALENGTH(<COLUMN>) - CHARINDEX(' ',<COLUMN>)) DESC
HTH
Jerry
"Brian Staff" <brianstaff AT [NoSpam]cox DOT net> wrote in message
news:VA.000002fd.102e316a@.bstaffw2k.jda.corp.local ...
>I have a table which contains a sNAME field - a persons name of "firstame
>space
> lastname".
> I have a query that delivers a recordset using "ORDER BY sName"
> Now, a client has asked to see the data sorted by "lastname". Is there any
> method using an SQL expression to deliver that recordset sorted by
> lastname?
> ..or do I have to re-organize my data fields and data?
> I was hoping for a quick 'sql function' solution instead of changing the
> database.
> Brian
>
|||Jerry,
Thanks...I think that will do for now. The table only has a max of 400 rows
in it.
Brian
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:u155VK20FHA.3000@.TK2MSFTNGP12.phx.gbl...
> Brian,
> You could use functions to determine the lastname and sort by that but
that
> technique would probably preclude the use of NC indexes to resolve the
query[vbcol=seagreen]
> and may negatively impact performance. Something like (or some variation
> of):
> SELECT <COLUMN LIST.
> FROM <TABLE>
> ORDER BY SUBSTRING(<COLUMN>, CHARINDEX(' ',<COLUMN>)+ 1,
> DATALENGTH(<COLUMN>) - CHARINDEX(' ',<COLUMN>)) DESC
> HTH
> Jerry
>
> "Brian Staff" <brianstaff AT [NoSpam]cox DOT net> wrote in message
> news:VA.000002fd.102e316a@.bstaffw2k.jda.corp.local ...
any
>
|||Jerry,
It worked and it was quick too...thanks again
Brian
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:u155VK20FHA.3000@.TK2MSFTNGP12.phx.gbl...
> Brian,
> You could use functions to determine the lastname and sort by that but
> that technique would probably preclude the use of NC indexes to resolve
> the query and may negatively impact performance. Something like (or some
> variation of):
> SELECT <COLUMN LIST.
> FROM <TABLE>
> ORDER BY SUBSTRING(<COLUMN>, CHARINDEX(' ',<COLUMN>)+ 1,
> DATALENGTH(<COLUMN>) - CHARINDEX(' ',<COLUMN>)) DESC
> HTH
> Jerry
>
> "Brian Staff" <brianstaff AT [NoSpam]cox DOT net> wrote in message
> news:VA.000002fd.102e316a@.bstaffw2k.jda.corp.local ...
>

ORDER BY Question

I have a table which contains a sNAME field - a persons name of "firstame space
lastname".
I have a query that delivers a recordset using "ORDER BY sName"
Now, a client has asked to see the data sorted by "lastname". Is there any
method using an SQL expression to deliver that recordset sorted by lastname?
..or do I have to re-organize my data fields and data?
I was hoping for a quick 'sql function' solution instead of changing the
database.
BrianBrian,
You could use functions to determine the lastname and sort by that but that
technique would probably preclude the use of NC indexes to resolve the query
and may negatively impact performance. Something like (or some variation
of):
SELECT <COLUMN LIST.
FROM <TABLE>
ORDER BY SUBSTRING(<COLUMN>, CHARINDEX(' ',<COLUMN>)+ 1,
DATALENGTH(<COLUMN>) - CHARINDEX(' ',<COLUMN>)) DESC
HTH
Jerry
"Brian Staff" <brianstaff AT [NoSpam]cox DOT net> wrote in message
news:VA.000002fd.102e316a@.bstaffw2k.jda.corp.local...
>I have a table which contains a sNAME field - a persons name of "firstame
>space
> lastname".
> I have a query that delivers a recordset using "ORDER BY sName"
> Now, a client has asked to see the data sorted by "lastname". Is there any
> method using an SQL expression to deliver that recordset sorted by
> lastname?
> ..or do I have to re-organize my data fields and data?
> I was hoping for a quick 'sql function' solution instead of changing the
> database.
> Brian
>|||Jerry,
Thanks...I think that will do for now. The table only has a max of 400 rows
in it.
Brian
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:u155VK20FHA.3000@.TK2MSFTNGP12.phx.gbl...
> Brian,
> You could use functions to determine the lastname and sort by that but
that
> technique would probably preclude the use of NC indexes to resolve the
query
> and may negatively impact performance. Something like (or some variation
> of):
> SELECT <COLUMN LIST.
> FROM <TABLE>
> ORDER BY SUBSTRING(<COLUMN>, CHARINDEX(' ',<COLUMN>)+ 1,
> DATALENGTH(<COLUMN>) - CHARINDEX(' ',<COLUMN>)) DESC
> HTH
> Jerry
>
> "Brian Staff" <brianstaff AT [NoSpam]cox DOT net> wrote in message
> news:VA.000002fd.102e316a@.bstaffw2k.jda.corp.local...
> >I have a table which contains a sNAME field - a persons name of "firstame
> >space
> > lastname".
> >
> > I have a query that delivers a recordset using "ORDER BY sName"
> >
> > Now, a client has asked to see the data sorted by "lastname". Is there
any
> > method using an SQL expression to deliver that recordset sorted by
> > lastname?
> >
> > ..or do I have to re-organize my data fields and data?
> >
> > I was hoping for a quick 'sql function' solution instead of changing the
> > database.
> >
> > Brian
> >
>|||Jerry,
It worked and it was quick too...thanks again
--
Brian
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:u155VK20FHA.3000@.TK2MSFTNGP12.phx.gbl...
> Brian,
> You could use functions to determine the lastname and sort by that but
> that technique would probably preclude the use of NC indexes to resolve
> the query and may negatively impact performance. Something like (or some
> variation of):
> SELECT <COLUMN LIST.
> FROM <TABLE>
> ORDER BY SUBSTRING(<COLUMN>, CHARINDEX(' ',<COLUMN>)+ 1,
> DATALENGTH(<COLUMN>) - CHARINDEX(' ',<COLUMN>)) DESC
> HTH
> Jerry
>
> "Brian Staff" <brianstaff AT [NoSpam]cox DOT net> wrote in message
> news:VA.000002fd.102e316a@.bstaffw2k.jda.corp.local...
>>I have a table which contains a sNAME field - a persons name of "firstame
>>space
>> lastname".
>> I have a query that delivers a recordset using "ORDER BY sName"
>> Now, a client has asked to see the data sorted by "lastname". Is there
>> any
>> method using an SQL expression to deliver that recordset sorted by
>> lastname?
>> ..or do I have to re-organize my data fields and data?
>> I was hoping for a quick 'sql function' solution instead of changing the
>> database.
>> Brian
>

ORDER BY question

I'm having some issuses with ORDER BY with my query. I'm trying to run the
same query in Oracle and SQL Server.
select a.APP_ID, a.USER_ID, SUM(a.CB) AS "CB", SUM(a.SB) AS "SB", SUM(a.CA)
AS "CA", SUM(a.SA) AS "SA" FROM APP_USER a WHERE a.START_TIME >=
1135044000000 AND a.START_TIME < 1135047600000 AND a.APP_ID = 56 AND GROUP_I
D
= 50 GROUP BY a.APP_ID, a.USER_ID ORDER BY sum(cb+sb) DESC
This work fine in SQL Server but not in Oracle, if I replace ORDER BY
sum(cb+sb) DESC with ORDER BY (cb+sb) DESC it works fine in Oracle but not
SQL Server. Any idea how I can achive the same results but one query to wor
k
in Oracle and SQL Server. Thanks.How about using a derived table?
Not sure if this is the exact syntax in Oracle (might want to post to an
Oracle group!) but this should work in SQL Server:
SELECT
APP_ID,
USER_ID,
CB,
SB,
CA,
SA
FROM
(
select
a.APP_ID,
a.USER_ID,
SUM(a.CB) AS "CB",
SUM(a.SB) AS "SB",
SUM(a.CA) AS "CA",
SUM(a.SA) AS "SA"
FROM
APP_USER a
WHERE
a.START_TIME >= 1135044000000
AND a.START_TIME < 1135047600000
AND a.APP_ID = 56
AND GROUP_ID = 50
GROUP BY
a.APP_ID,
a.USER_ID
) x
ORDER BY
CB+SB DESC;
"yodarules" <yodarules@.discussions.microsoft.com> wrote in message
news:D4C09490-E189-4524-95FF-744CCE7F3A7A@.microsoft.com...
> I'm having some issuses with ORDER BY with my query. I'm trying to run
> the
> same query in Oracle and SQL Server.
> select a.APP_ID, a.USER_ID, SUM(a.CB) AS "CB", SUM(a.SB) AS "SB",
> SUM(a.CA)
> AS "CA", SUM(a.SA) AS "SA" FROM APP_USER a WHERE a.START_TIME >=
> 1135044000000 AND a.START_TIME < 1135047600000 AND a.APP_ID = 56 AND
> GROUP_ID
> = 50 GROUP BY a.APP_ID, a.USER_ID ORDER BY sum(cb+sb) DESC
> This work fine in SQL Server but not in Oracle, if I replace ORDER BY
> sum(cb+sb) DESC with ORDER BY (cb+sb) DESC it works fine in Oracle but not
> SQL Server. Any idea how I can achive the same results but one query to
> work
> in Oracle and SQL Server. Thanks.|||"yodarules" <yodarules@.discussions.microsoft.com> wrote in message
news:D4C09490-E189-4524-95FF-744CCE7F3A7A@.microsoft.com...
> I'm having some issuses with ORDER BY with my query. I'm trying to run
> the
> same query in Oracle and SQL Server.
> select a.APP_ID, a.USER_ID, SUM(a.CB) AS "CB", SUM(a.SB) AS "SB",
> SUM(a.CA)
> AS "CA", SUM(a.SA) AS "SA" FROM APP_USER a WHERE a.START_TIME >=
> 1135044000000 AND a.START_TIME < 1135047600000 AND a.APP_ID = 56 AND
> GROUP_ID
> = 50 GROUP BY a.APP_ID, a.USER_ID ORDER BY sum(cb+sb) DESC
> This work fine in SQL Server but not in Oracle, if I replace ORDER BY
> sum(cb+sb) DESC with ORDER BY (cb+sb) DESC it works fine in Oracle but not
> SQL Server. Any idea how I can achive the same results but one query to
> work
> in Oracle and SQL Server. Thanks.
Try:
SELECT app_id, user_id, cb, sb, ca, sa
FROM
(SELECT a.APP_ID, a.USER_ID, SUM(a.CB) AS "CB", SUM(a.SB) AS "SB",
SUM(a.CA) AS "CA", SUM(a.SA) AS "SA",
SUM(a.CB)+SUM(a.SB) AS ord
FROM APP_USER a
WHERE a.START_TIME >= 1135044000000
AND a.START_TIME < 1135047600000
AND a.APP_ID = 56 AND GROUP_ID = 50
GROUP BY a.APP_ID, a.USER_ID) AS T
ORDER BY ord DESC ;
David Portas
SQL Server MVP
--|||"yodarules" <yodarules@.discussions.microsoft.com> wrote in message
news:D4C09490-E189-4524-95FF-744CCE7F3A7A@.microsoft.com...
> I'm having some issuses with ORDER BY with my query. I'm trying to run
> the
> same query in Oracle and SQL Server.
> select a.APP_ID, a.USER_ID, SUM(a.CB) AS "CB", SUM(a.SB) AS "SB",
> SUM(a.CA)
> AS "CA", SUM(a.SA) AS "SA" FROM APP_USER a WHERE a.START_TIME >=
> 1135044000000 AND a.START_TIME < 1135047600000 AND a.APP_ID = 56 AND
> GROUP_ID
> = 50 GROUP BY a.APP_ID, a.USER_ID ORDER BY sum(cb+sb) DESC
> This work fine in SQL Server but not in Oracle, if I replace ORDER BY
> sum(cb+sb) DESC with ORDER BY (cb+sb) DESC it works fine in Oracle but not
> SQL Server. Any idea how I can achive the same results but one query to
> work
> in Oracle and SQL Server. Thanks.
I don't know if this will work, but you might try:
Order by 3 DESC
3 being the column's ordinal position in the SELECT list.
Rick Sawtell|||how about just adding a column for
sum(a.CB+a.SB) as CBSB
and then
order by CBSB desc
yodarules wrote:
> I'm having some issuses with ORDER BY with my query. I'm trying to run th
e
> same query in Oracle and SQL Server.
> select a.APP_ID, a.USER_ID, SUM(a.CB) AS "CB", SUM(a.SB) AS "SB", SUM(a.CA
)
> AS "CA", SUM(a.SA) AS "SA" FROM APP_USER a WHERE a.START_TIME >=
> 1135044000000 AND a.START_TIME < 1135047600000 AND a.APP_ID = 56 AND GROUP
_ID
> = 50 GROUP BY a.APP_ID, a.USER_ID ORDER BY sum(cb+sb) DESC
> This work fine in SQL Server but not in Oracle, if I replace ORDER BY
> sum(cb+sb) DESC with ORDER BY (cb+sb) DESC it works fine in Oracle but not
> SQL Server. Any idea how I can achive the same results but one query to w
ork
> in Oracle and SQL Server. Thanks.|||Thanks guys,
I already tried that, but adding another columns in the select list is ruled
out, since we don't need the sum of these there. Its only for display
purpose that we need the sum of these two columns.
The reply by Rick, what I need is the sum of the two columns, in your case
giving the position is only going to do it for that one column.
"Trey Walpole" wrote:

> how about just adding a column for
> sum(a.CB+a.SB) as CBSB
> and then
> order by CBSB desc
>
> yodarules wrote:
>|||I don't understand the 'x' before the ORDER BY, could you explain that pleas
e.
"Aaron Bertrand [SQL Server MVP]" wrote:

> How about using a derived table?
> Not sure if this is the exact syntax in Oracle (might want to post to an
> Oracle group!) but this should work in SQL Server:
>
> SELECT
> APP_ID,
> USER_ID,
> CB,
> SB,
> CA,
> SA
> FROM
> (
> select
> a.APP_ID,
> a.USER_ID,
> SUM(a.CB) AS "CB",
> SUM(a.SB) AS "SB",
> SUM(a.CA) AS "CA",
> SUM(a.SA) AS "SA"
> FROM
> APP_USER a
> WHERE
> a.START_TIME >= 1135044000000
> AND a.START_TIME < 1135047600000
> AND a.APP_ID = 56
> AND GROUP_ID = 50
> GROUP BY
> a.APP_ID,
> a.USER_ID
> ) x
> ORDER BY
> CB+SB DESC;
>
> "yodarules" <yodarules@.discussions.microsoft.com> wrote in message
> news:D4C09490-E189-4524-95FF-744CCE7F3A7A@.microsoft.com...
>
>|||a derived table must have an alias - 'x' is as good as any. :)
yodarules wrote:
> I don't understand the 'x' before the ORDER BY, could you explain that ple
ase.
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||>I don't understand the 'x' before the ORDER BY, could you explain that
>please.
>
The X is an alias for the derived table (the bracketed query after the FROM
clause).
David Portas
SQL Server MVP
--|||so don't display it :)
yodarules wrote:
> Thanks guys,
> I already tried that, but adding another columns in the select list is rul
ed
> out, since we don't need the sum of these there. Its only for display
> purpose that we need the sum of these two columns.
> The reply by Rick, what I need is the sum of the two columns, in your cas
e
> giving the position is only going to do it for that one column.
> "Trey Walpole" wrote:
>