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 values in IN clause

Hi All,
I have a problem [stated below].
select '$'+CONVERT(VARCHAR,SUM(mMoney),1) from trans where TS IN
('2001','23456789') GROUP BY TS
select '$'+CONVERT(VARCHAR,SUM(mMoney),1) from trans where TS IN
('23456789','2001') GROUP BY TS
both return same result set.
$99,200.00
$4,343.00
I need to order the result set by the values that I give in the IN
clause. Is this possible?Anybody could please explain why this is
happening and what would be the remedy.
Thanks in advance.
Thanks & Regards,
Shankar.> I need to order the result set by the values that I give in the IN
> clause. Is this possible?Anybody could please explain why this is
> happening and what would be the remedy.
SQL Server is free to return results in any order unless you specify ORDER
BY. This is regardless of the order of values in your IN clause, order of
joined tables, table indexes, etc.
One solution is to add an additional value for the desired sequencing so
that you can specify ORDER BY. The example below uses a derived table:
SELECT '$'+CONVERT(VARCHAR,SUM(mMoney),1)
FROM trans
JOIN (SELECT '2001' AS TS, 1 AS Seq
UNION ALL SELECT '23456789', 2) AS trans_list
ON trans.TS = trans_list.TS
GROUP BY trans_list.TS, trans_list.Seq
ORDER BY trans_list.Seq
BTW, formatting data for display purposes is a job best done in the
presentation layer rather than in the database. That approach is more
scalable. Also, front-end tools (e.g. Reporting Services) provide much
richer formatting capability than Transact-SQL.
Hope this helps.
Dan Guzman
SQL Server MVP
<shankararaman.s@.gmail.com> wrote in message
news:1146824029.416992.301160@.j73g2000cwa.googlegroups.com...
> Hi All,
> I have a problem [stated below].
> select '$'+CONVERT(VARCHAR,SUM(mMoney),1) from trans where TS IN
> ('2001','23456789') GROUP BY TS
> select '$'+CONVERT(VARCHAR,SUM(mMoney),1) from trans where TS IN
> ('23456789','2001') GROUP BY TS
> both return same result set.
> $99,200.00
> $4,343.00
> I need to order the result set by the values that I give in the IN
> clause. Is this possible?Anybody could please explain why this is
> happening and what would be the remedy.
>
> Thanks in advance.
> Thanks & Regards,
> Shankar.
>|||try this.
select '$'+CONVERT(VARCHAR,SUM(mMoney),1) from trans where TS IN
('23456789','2001') GROUP BY TS
order by TS|||Also format the data in your front end application
Madhivanansql

Order By Update Error

Hi Every1,
I'm getting the following error
Server: Msg 1033, Level 15, State 1, Line 13
The ORDER BY clause is invalid in views, inline functions, derived
tables, and subqueries, unless TOP is also specified.
what I'm trying to do is to update a table based on the select criteria
I have. In that I'm using ORDER BY Clause & this is giving me error.
Here is my sql:
SELECT SUBSTRING(A.DESCR,1,10), B.SUPPORT_TEAM_MBR, EMPLID, COUNT(*)
from PS_TEAM_CODE_TBL A, PS_TEAM_MEMBERS B, PS_MEMBER_PERSON C
WHERE A.SUPPORT_TEAM_CD = B.SUPPORT_TEAM_CD
AND A.EFF_STATUS = 'A'
AND A.EFFDT >= (SELECT MAX(AX.EFFDT) FROM PS_TEAM_CODE_TBL AX
WHERE A.SETID = AX.SETID
AND A.SUPPORT_TEAM_CD = AX.SUPPORT_TEAM_CD)
AND B.SUPPORT_TEAM_MBR = C.SUPPORT_TEAM_MBR
GROUP BY SUBSTRING(A.DESCR,1,10), B.SUPPORT_TEAM_MBR, EMPLID
HAVING COUNT(*) > 1
ORDER BY SUBSTRING(A.DESCR,1,10)
Is it possible to fix this error?
Thanks in advance for your help.Tony,
The query you posted is not an UPDATE statement, View or Function.
I don't see any problems with the query you posted. However, it is not
valid as a view definition, because of the ORDER BY clause, missing
column names for the resultset and maybe more.
In what way to you think that the order is important when updating a
table? What is the actual UPDATE statement you are trying to use?
Gert-Jan
Tony Schplik wrote:
> Hi Every1,
> I'm getting the following error
> Server: Msg 1033, Level 15, State 1, Line 13
> The ORDER BY clause is invalid in views, inline functions, derived
> tables, and subqueries, unless TOP is also specified.
> what I'm trying to do is to update a table based on the select criteria
> I have. In that I'm using ORDER BY Clause & this is giving me error.
> Here is my sql:
> SELECT SUBSTRING(A.DESCR,1,10), B.SUPPORT_TEAM_MBR, EMPLID, COUNT(*)
> from PS_TEAM_CODE_TBL A, PS_TEAM_MEMBERS B, PS_MEMBER_PERSON C
> WHERE A.SUPPORT_TEAM_CD = B.SUPPORT_TEAM_CD
> AND A.EFF_STATUS = 'A'
> AND A.EFFDT >= (SELECT MAX(AX.EFFDT) FROM PS_TEAM_CODE_TBL AX
> WHERE A.SETID = AX.SETID
> AND A.SUPPORT_TEAM_CD = AX.SUPPORT_TEAM_CD)
> AND B.SUPPORT_TEAM_MBR = C.SUPPORT_TEAM_MBR
> GROUP BY SUBSTRING(A.DESCR,1,10), B.SUPPORT_TEAM_MBR, EMPLID
> HAVING COUNT(*) > 1
> ORDER BY SUBSTRING(A.DESCR,1,10)
> Is it possible to fix this error?
> Thanks in advance for your help.|||Gert-Jan,
Thanks for your response. Sorry I forgot to put the update statement.
Here it is
UPDATE Name1
SET NAME1 = 'Z' FROM PS_MEMBER_PERSON WHERE NAME1 =(SELECT SUBSTRING(A.DESCR,1,10), B.SUPPORT_TEAM_MBR, EMPLID, COUNT(*)
from PS_TEAM_CODE_TBL A, PS_TEAM_MEMBERS B, PS_MEMBER_PERSON C
WHERE A.SUPPORT_TEAM_CD = B.SUPPORT_TEAM_CD
AND A.EFF_STATUS = 'A'
AND A.EFFDT >= (SELECT MAX(AX.EFFDT) FROM PS_TEAM_CODE_TBL AX
WHERE A.SETID = AX.SETID
AND A.SUPPORT_TEAM_CD = AX.SUPPORT_TEAM_CD)
AND B.SUPPORT_TEAM_MBR = C.SUPPORT_TEAM_MBR
GROUP BY SUBSTRING(A.DESCR,1,10), B.SUPPORT_TEAM_MBR, EMPLID
HAVING COUNT(*) > 1
ORDER BY SUBSTRING(A.DESCR,1,10)
Even if I take out the order by clause in the update, then it will give
me a different error for the subquery
Do you have any suggestions for that ...
Thanks in advance for your help|||Tony,
Have you looked into the syntax of the UPDATE statement in SQL Server Books
Online?
--
Anithsql

Order By nvarchar

I have a scenario to sort on an nvarchar (50) field and I need to keep any changes to the sproc in the ORDER BY clause if possible. This field will contain strings such as...

abc-217c, abc-15a, abc-9a, abc-7b, abc-17ar, etc...

The issue I'm having is when the records are sorted, they are returned as...

abc-15a, abc-17ar, abc-217c, abc-7b, abc-9a,etc...ordering numerically on the first numeric character in the string ie, 1,1,2,7,9)

However, I need the numeric component to be treated as a whole number and order in this fashion...

abc-7b, abc-9a, abc-15a, abc-17ar, abc-217c (7,9,15,17,217, etc)

I feel pretty sure that this issue has come up before...can anybody provide a working example that would provide a simple(or not so simple) solution?

Hello my friend,

The SQL to do this would be very complicated. If I were you, I would create extra fields in the table where these codes come from to hold parts of the code. For example: -

FullCode Name Number Section

abc-217c abc 217 c

By breaking this down, you can still select the FullCode and order by Name, Number, Section so it comes out right.

Kind regards

Scotty

|||

try code below:

create

table #test(aaanvarchar(50))

insert

into #test

Values

('abc-217c')

insert

into #test

Values

('abc-15a')

insert

into #test

Values

('abc-9a')

insert

into #test

Values

('abc-7b')

insert

into #test

Values

('abc-17ar')

select

*,substring(aaa, 1,charindex('-',aaa))+

case

whenisnumeric(substring(aaa,charindex('-',aaa)+1,4))= 1then'0'+substring(aaa,charindex('-',aaa)+1,4)whenisnumeric(substring(aaa,charindex('-',aaa)+1,3))= 1then'00'+substring(aaa,charindex('-',aaa)+1,3)whenisnumeric(substring(aaa,charindex('-',aaa)+1,2))= 1then'000'+substring(aaa,charindex('-',aaa)+1,2)whenisnumeric(substring(aaa,charindex('-',aaa)+1,1))= 1then'0000'+substring(aaa,charindex('-',aaa)+1,1)

else

''

end

+

case

whenisnumeric(substring(aaa,charindex('-',aaa)+1,4))= 1thensubstring(aaa,charindex('-',aaa)+5,50)whenisnumeric(substring(aaa,charindex('-',aaa)+1,3))= 1thensubstring(aaa,charindex('-',aaa)+4,50)whenisnumeric(substring(aaa,charindex('-',aaa)+1,2))= 1thensubstring(aaa,charindex('-',aaa)+3,50)whenisnumeric(substring(aaa,charindex('-',aaa)+1,1))= 1thensubstring(aaa,charindex('-',aaa)+2,50)

else

''

end

fieldToOrderBYfrom #test

order

by fieldToOrderBY

drop

table #test

Good luck

sql

Order By madness

I have a simple case statement which changes the "ORDER BY" clause based on
a parameter passed to a stored proc. 4 out of 5 cases work, but one fails
with the following:
Server: Msg 295, Level 16, State 3, Procedure pr_reportPartnersDBTest, Line
12
Syntax error converting character string to smalldatetime data type.
If I comment out the case block and put each ORDER BY as an independent
statement (uncommenting one at a time), they all work without error.
When the case block is used and the int 4 is passed (my zip field), I
receive the error message above. If I comment out the case statement and
just use "ORDER BY zip" it works fine.
I must be missing something obvious, but I'm too close and suspect I'm
missing something that someone else will immediately see.
All comments, ideas or thoughts welcomed.
-- CODE snippet --
select ...
from ...
where...
order by
case @.orderBy
when 1 then dateRegistered
when 2 then gender
when 3 then bodyType
when 4 then zip
when 5 then ageRange
else dateRegistered
end
-- The next lines are for debugging purposes...
-- I commented out the case block above, then tested each of the lines below
one at a time. They all work as expected. But the case statement above fails
when I pass it a 4 (zip). All other values work as expected.
-- order by dateRegistered
-- order by gender
-- order by bodyType
-- order by zip
-- order by ageRangeCASE in SQL Server is an expression that returns a scalar value of a
specific datatype. That means that if the separate expressions for each WHEN
have a different datatype, they will explicitly be converted to the datatype
with the highest datatype precedence, in your case smalldatetime. And you
can't convert all zipcodes into a smalldatetime value.
So the simplest way to do it is to have a separate CASE for each column:
order by
case @.orderBy when 2 then gender end,
case @.orderBy when 3 then bodyType end,
case @.orderBy when 4 then zip end,
case @.orderBy when 5 then ageRange end,
case when @.OrderBy NOT IN (2,3,4,5) then dateRegistered end
Jacco Schalkwijk
SQL Server MVP
"Don B" <DonBaarns@.hotmail.com> wrote in message
news:OlsEGQOUFHA.1432@.TK2MSFTNGP09.phx.gbl...
>I have a simple case statement which changes the "ORDER BY" clause based on
> a parameter passed to a stored proc. 4 out of 5 cases work, but one fails
> with the following:
> Server: Msg 295, Level 16, State 3, Procedure pr_reportPartnersDBTest,
> Line
> 12
> Syntax error converting character string to smalldatetime data type.
> If I comment out the case block and put each ORDER BY as an independent
> statement (uncommenting one at a time), they all work without error.
> When the case block is used and the int 4 is passed (my zip field), I
> receive the error message above. If I comment out the case statement and
> just use "ORDER BY zip" it works fine.
> I must be missing something obvious, but I'm too close and suspect I'm
> missing something that someone else will immediately see.
> All comments, ideas or thoughts welcomed.
> -- CODE snippet --
> select ...
> from ...
> where...
> order by
> case @.orderBy
> when 1 then dateRegistered
> when 2 then gender
> when 3 then bodyType
> when 4 then zip
> when 5 then ageRange
> else dateRegistered
> end
> -- The next lines are for debugging purposes...
> -- I commented out the case block above, then tested each of the lines
> below
> one at a time. They all work as expected. But the case statement above
> fails
> when I pass it a 4 (zip). All other values work as expected.
>
> -- order by dateRegistered
> -- order by gender
> -- order by bodyType
> -- order by zip
> -- order by ageRange
>|||It is also worth mention that a CASE is not allowed in the ORDER BY in
Standard SQL-92. You can put it in a column in the select list, name
it and use that name in the ORDER BY. This way your code will port, be
readable to people who do not speak T-SQL dialect and your user will be
able to tell what the sort was done on, instead of trying to guess.
In the olde days, we would print the sort key at both the left and
right sides of a print out, so you could lay a ruler acorss the 132
column page. The printout were pretty fuzzy at times and this really
helped. Today, I assume that the sort column would be on the right and
locked so you can scroll horizonally.|||Jacco,
EXCELLENT... I clearly don't know enough fundamentals about T-SQL. That
works great for my example.
I would really like a secondary sort on zip code (a varchar in my case)
which would be something like this:
order by zip, dateRegistered
and/or
order by bodyType, zip
The two statements above work as expected.
I can't just put the two columns with a comma inside the Then/End
combination as the case statement uses the comma as a delimiter. I don't
know how to escape it (or the equivalent) so the case statement can return a
set of columns for the order by clause. I may also want to change the
ASC/DESC order on specific columns but I assume if I figure out the syntax
for the multiple columns it will be similar if I add specific sort orders
for some columns.
All ideas welcomed!
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:O0KSlYOUFHA.2892@.TK2MSFTNGP14.phx.gbl...
> CASE in SQL Server is an expression that returns a scalar value of a
> specific datatype. That means that if the separate expressions for each
WHEN
> have a different datatype, they will explicitly be converted to the
datatype
> with the highest datatype precedence, in your case smalldatetime. And you
> can't convert all zipcodes into a smalldatetime value.
> So the simplest way to do it is to have a separate CASE for each column:
> order by
> case @.orderBy when 2 then gender end,
> case @.orderBy when 3 then bodyType end,
> case @.orderBy when 4 then zip end,
> case @.orderBy when 5 then ageRange end,
> case when @.OrderBy NOT IN (2,3,4,5) then dateRegistered end
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Don B" <DonBaarns@.hotmail.com> wrote in message
> news:OlsEGQOUFHA.1432@.TK2MSFTNGP09.phx.gbl...
on
fails
>|||Use the same approach:
order by
case @.orderBy when 1 then zip end,
case @.orderBy when 1 then dateRegistered end,
case @.orderBy when 2 then bodyType end,
case @.orderBy when 2 then zip end ASC,
case @.orderBy when 3 then bodyType end,
case @.orderBy when 3 then zip end DESC
Terri Morton
MVP - ASP/ASP.NET
"Don B" <DonBaarns@.hotmail.com> wrote in message
news:OrWlLtOUFHA.3716@.TK2MSFTNGP12.phx.gbl...
> Jacco,
> EXCELLENT... I clearly don't know enough fundamentals about T-SQL. That
> works great for my example.
> I would really like a secondary sort on zip code (a varchar in my case)
> which would be something like this:
> order by zip, dateRegistered
> and/or
> order by bodyType, zip
> The two statements above work as expected.
> I can't just put the two columns with a comma inside the Then/End
> combination as the case statement uses the comma as a delimiter. I don't
> know how to escape it (or the equivalent) so the case statement can return
> a
> set of columns for the order by clause. I may also want to change the
> ASC/DESC order on specific columns but I assume if I figure out the syntax
> for the multiple columns it will be similar if I add specific sort orders
> for some columns.
> All ideas welcomed!
>
>
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> wrote
> in message news:O0KSlYOUFHA.2892@.TK2MSFTNGP14.phx.gbl...
> WHEN
> datatype

Order By items not in the select list

Books online says this about "Order By":
The ORDER BY clause can include items not appearing in the select list.
Well, that's actually strange and surprising. This is surely not new to
many of you, but I just recently saw this construction:
Use Pubs
Select Top 5 * From Titles Order by Newid()
Which, of course, picks 5 titles randomly from the Titles table. It might
not be efficient for huge tables, but it's good to know that you can do
this. Other than this, I have NEVER seen an Order By that didn't reference
a column from the Select list, and I had no idea that it was possible -- I
must have glossed over that sentence in BOL since it had no more
explanation and it wouldn't have really make sense before I knew about it
(cognitive dissonance, I suppose).
Note: If you're using Windows 2000 or later, the implementation of Newid()
returns random IDs. In earlier operating systems, I think they were
sequential.
You have to read between the lines of that Select statement to figure out
that SQL must append the expression you supply to each row of the table,
then order the result set by that expression. Not that BOL bothers to
explain this, of course.
It's kind of poor that BOL has this one sentence but doesn't go on to
explain what happens when you order by something not in the select list.
Can I order by the values taken from a field in a different table? (Not as
far as I can tell.)
Can you do this:
Select Top 5 * From Titles Order by 'Hello there'
(Yes, you can, but it's not interesting.)
BOL should say "The ORDER BY clause can include items not appearing in the
select list, and when you do that, here's what happens..."
Are there any other cases where this feature of Order By could be useful,
other than with Newid()?
David Walker"DWalker" <none@.none.com> wrote in message
news:%23UutkJ$MFHA.3512@.TK2MSFTNGP15.phx.gbl...
> It's kind of poor that BOL has this one sentence but doesn't go on to
> explain what happens when you order by something not in the select list.
> Can I order by the values taken from a field in a different table? (Not
as
> far as I can tell.)
> Are there any other cases where this feature of Order By could be useful,
> other than with Newid()?
You can ORDER BY any valid scalar.
Yes, you can order by a value from another table. Use a correlated
subquery:
ORDER BY
(SELECT SomeColumn
FROM SomeOtherTable
WHERE SomeOtherTable.PK = YourTable.PK)
Or, it's often very useful to ORDER BY a CASE expression... For
instance, maybe you want to return all rows where SomeColumn = 99 first,
then sort the rest by SomeColumn ascending:
ORDER BY
CASE SomeColumn
WHEN 99 THEN 1
ELSE SomeColumn
END
.. There are lots of interesting things you can do.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||To add to Adam's reply, In general, this should occur anytime you want th
e
rows in an output resultset ordered by some value (albiet a direct Column
value or calculated expression) whose value you are not specifically
interested in on the CLient, other than as a mechanism by which to sort the
rows...
I often Order by a datetime expression, which is not output in the Select
clause,
It is frequently required that an Output column based on a datetime be
formatted as a user readable string, say "Tuesday, March 14" While actually
sorting on the internal datetime value of the same column, not on the string
which is output by the SQL .
"DWalker" wrote:

> Books online says this about "Order By":
> The ORDER BY clause can include items not appearing in the select list.
> Well, that's actually strange and surprising. This is surely not new to
> many of you, but I just recently saw this construction:
> Use Pubs
> Select Top 5 * From Titles Order by Newid()
> Which, of course, picks 5 titles randomly from the Titles table. It might
> not be efficient for huge tables, but it's good to know that you can do
> this. Other than this, I have NEVER seen an Order By that didn't referenc
e
> a column from the Select list, and I had no idea that it was possible -- I
> must have glossed over that sentence in BOL since it had no more
> explanation and it wouldn't have really make sense before I knew about it
> (cognitive dissonance, I suppose).
> Note: If you're using Windows 2000 or later, the implementation of Newid()
> returns random IDs. In earlier operating systems, I think they were
> sequential.
> You have to read between the lines of that Select statement to figure out
> that SQL must append the expression you supply to each row of the table,
> then order the result set by that expression. Not that BOL bothers to
> explain this, of course.
> It's kind of poor that BOL has this one sentence but doesn't go on to
> explain what happens when you order by something not in the select list.
> Can I order by the values taken from a field in a different table? (Not a
s
> far as I can tell.)
> Can you do this:
> Select Top 5 * From Titles Order by 'Hello there'
> (Yes, you can, but it's not interesting.)
> BOL should say "The ORDER BY clause can include items not appearing in the
> select list, and when you do that, here's what happens..."
> Are there any other cases where this feature of Order By could be useful,
> other than with Newid()?
>
> David Walker
>|||Thanks, Adam and cbretana. That helps.
David
"examnotes" <cbretana@.areteIndNOSPAM.com> wrote in
news:7B887787-2D11-4886-8CE7-C007F4F93603@.microsoft.com:
> To add to Adam's reply, In general, this should occur anytime you
> want the rows in an output resultset ordered by some value (albiet a
> direct Column value or calculated expression) whose value you are not
> specifically interested in on the CLient, other than as a mechanism by
> which to sort the rows...
> I often Order by a datetime expression, which is not output in the
> Select clause,
> It is frequently required that an Output column based on a datetime be
> formatted as a user readable string, say "Tuesday, March 14" While
> actually sorting on the internal datetime value of the same column,
> not on the string which is output by the SQL .
>
> "DWalker" wrote:
>|||On Tue, 29 Mar 2005 08:08:53 -0800, DWalker wrote:

>Thanks, Adam and cbretana. That helps.
>David
Hi David,
In addition to Adam's and CBretana's reply, I'd like to point out that
the ability to order by items that are not in the select list is a T-SQL
proprietary feature. If portability of your code is important, then it's
best to include the ordering columns in the select list, so that your
query is ANSI-compliant.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:rsfj41l58bdeg94ppt37vgvud1t4814335@.
4ax.com...
> In addition to Adam's and CBretana's reply, I'd like to point out that
> the ability to order by items that are not in the select list is a T-SQL
> proprietary feature. If portability of your code is important, then it's
> best to include the ordering columns in the select list, so that your
> query is ANSI-compliant.
Maybe not ANSI-compliant, but I think "proprietary" is too harsh a term.
This is fairly widely supported amongst SQL DBMSs -- Oracle, DB2,
PostgreSQL, and others all support this, in addition to SQL Server.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||OK, thanks. I'm sure we won't be moving this out of SQL 2000 except
possibly to SQL 2005, but it's good to know it's not strictly ANSI-
compliant.
David
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in
news:OBsRlIKNFHA.244@.tk2msftngp13.phx.gbl:

> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:rsfj41l58bdeg94ppt37vgvud1t4814335@.
4ax.com...
> Maybe not ANSI-compliant, but I think "proprietary" is too harsh a
> term.
> This is fairly widely supported amongst SQL DBMSs -- Oracle, DB2,
> PostgreSQL, and others all support this, in addition to SQL Server.
>

Order by in sql 2005 views = don't use?

I shudder to bring this issue up, but in very simple terms:
Saving a "view" in sql 2005 with a order by clause WILL NOT guarantee any
particular order of data if I in fact query that view via some sql? Is this
correct?
Eg:
Select * from myView
Is my assumption correct? And this is a notable change from sql 2000 in
which the order of a view was respected? Note that I am well aware this
practice in sql 2000 NEVER should have been widely used, but it was (and I
suppose it just been pure luck that views kept their order of data
returned).
I should WELL note that I perfectly understand that a rdbms is a un-ordered
hunk of data. If you need data in some order, you add an order by clause to
the sql request. This is *perfectly* clear, and perfectly normal to me. This
issue of setting order of data comes up a lot in the access newsgroups also.
However, I was NOT aware that a view is ALSO simply considered a table, and
as such an un-order hunk of data also! Does this mean again that the
developer MUST specify the order when retrieving data, *even* from a view,
and EVEN when that view has a order by clause? I also note that views also
"now" require the obligatory top 100 command, and again this requirement
supports the idea that order by in the view does not make sense!
Is the above a correct view (pun intended) that setting a order by clause in
a saved view don't amount to a hill of beans and cannot be relied upon?
I ask the above, because in a lot of vb6 projects that connected to SQL
server 2000, it was very common to use a view as a way to store tons and
tons of SQL statements. In other words this approach was used to simply not
have inline SQL in the application. I should point out that there is a
significant difference in using server views for saving a whole bunch of SQL
statements you hope to execute in the future, as Opposed to that of a
actually needing a real database view. This "view" feature was Obviously
abused in the past.
In many cases we developers simply used those views to store our SQL, and
we're
not really interested in the actual technical details of what a TRUE
database view is supposed to represent. In retrospect, it probably would
have been far more intelligent to develop one's own "sql" data store that
saves the sql for use with code in some data store.
I apologize for the lengthy post, but it seems to me that the conceptual
concept of views used in SQL server 2005 means that we developers should not
use views as a dumping ground in which to save all kinds of SQL select
statements. This was a Common practice in the past, it seems to me now that
this approach should be avoided in the future.
Am I reading this correct? (or can I still risk relying on a view to
return ordered data for me?).
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@.msn.com
"Albert D. Kallal" <PleaseNOOOsPAMmkallal@.msn.com> wrote in message
news:eYfl1oIcIHA.1376@.TK2MSFTNGP02.phx.gbl...
>I shudder to bring this issue up, but in very simple terms:
> Saving a "view" in sql 2005 with a order by clause WILL NOT guarantee any
> particular order of data if I in fact query that view via some sql? Is
> this
> correct?
> Eg:
> Select * from myView
>
[snip]
It is correct that the ordering of rows returned by ANY query is undefined
unless that query includes ORDER BY. Obviously the fact that a tabular
result is displayed on the screen means that some kind of ordering is
shown - but you cannot and should not rely on it always being the same. That
is true irrespective of whether the query references tables or views and it
applies equally to all versions: 7.0, 2000, 2005, 2008.

> And this is a notable change from sql 2000 in
> which the order of a view was respected?
It is not a change. There is no such thing as the "order of a view".

> However, I was NOT aware that a view is ALSO simply considered a table,
> and
> as such an un-order hunk of data also! Does this mean again that the
> developer MUST specify the order when retrieving data, *even* from a view,
> and EVEN when that view has a order by clause?
In relational terms a view and a table are one and the same - they are both
relations and relations are unordered sets of tuples.
SQL Server 2000 introduced a very silly syntax for the TOP clause, which
used ORDER BY to specify the criteria used to select a subset of n or n%
rows. Unfortunately this has caused endless confusion and led some people to
assume that ordering of a view was possible - even though Microsoft never
claimed any such thing and even though the concept of an ordered view is
alien to anyone familiar with the fundamentals of SQL or the relational
model.

> I also note that views also
> "now" require the obligatory top 100 command
That is false. There is no such requirement. I expect you are using the view
designer "feature" that does insert TOP 100 PERCENT in some cases.
Personally I would never use the view designer. The fact that it messes with
your query syntax and disallows certain valid constructs is reason enough to
avoid the designer altogether IMO.
There are many previous discussions on this topic, all reiterating much the
same points.
David Portas
|||In addition to David's notes, SQL Server 2005 Books Online has this
summarized very well in one note (under CREATE VIEW):
"The ORDER BY clause is used only to determine the rows that are returned by
the TOP clause in the view definition. The ORDER BY clause does not
guarantee ordered results when the view is queried, unless ORDER BY is also
specified in the query itself."
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message

> That is false. There is no such requirement. I expect you are using the
> view designer "feature" that does insert TOP 100 PERCENT in some cases.
> Personally I would never use the view designer. The fact that it messes
> with your query syntax and disallows certain valid constructs is reason
> enough to avoid the designer altogether IMO.
Thank you very much for your comments on this matter.
Ouch!! I understand perfectly your respsone, and agree. However, advoid the
desinger is a ouch for me!!!
As I commemned, many a developers relied on those views to return data in
particular order, and as you commented it should never been done that way
(but, it *was* common despite the fact that we should not been doing it this
way).
However the comments about avoiding the designer seems another kind of
difficult pill to swallow. If I am not to use the query designer as crutch
to have "fun" to build quiers by dragging and dropping my fields and
dropping in tables, then what am I supposed to use here? In other words I
love those query designers, and to be honest as a general rule it makes
sense to use tools that do most of the work for me.
I have relied on query builders to do most the work for me in just about a
every development environment that I used sql in (and it been a LOT of
systems). (SQL, FoxPro, ms-access, enterprise tools, MySql tools, this list
is RATHER long).
It just seems to me that building SQL by hand is like going back to
developing web pages by hand coding your HTML. You might tweak the HTML (or
sql) by hand, but you likey should start with some type of development and
building tool here. This is really a cost of human time versus machine
computer time.
I now kind of wonder what direction most developers are taking when they are
developing applications with many many SQL queries, and where do most people
store and place those queries? As I mentioned in the past it was common to
use those views as a storage mechanism -- and I well know it's been
rightfully pointed out that views should never have been used as a
repository for those SQL statements.
However, typing in freeform sql and saving that sql in some text system
makes little sense without some tools to aid in the desing and syntax of
that sql. It is just not productive, and I am not quite sure what direction
I should take now in this regards. (I suppose with visual studio there is
linQ, but that's another matter again).
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@.msn.com
|||Hi Albert
It seems that you code is most likely to have been based on single statement
queries rather than stored procedures that contain multiple statements?
You should find that after a while using the query window or some other tool
that you wonder why you ever used the query builder, expecially as there are
templates and the drag and drop features of the object explorer (where you
can pull database/tables/column names across into the window.
You may want to watch a few of the shorts on jumpstarttv.com such a Brian's
intoduction to management studio
http://www.jumpstarttv.com/Media.aspx?vid=58 or more generic T-SQL videos by
Kathi Kellenberger http://www.jumpstarttv.com/Media.aspx?vid=335
John
"Albert D. Kallal" <PleaseNOOOsPAMmkallal@.msn.com> wrote in message
news:u7tNbiOcIHA.1376@.TK2MSFTNGP02.phx.gbl...
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
>
> Thank you very much for your comments on this matter.
> Ouch!! I understand perfectly your respsone, and agree. However, advoid
> the desinger is a ouch for me!!!
> As I commemned, many a developers relied on those views to return data in
> particular order, and as you commented it should never been done that way
> (but, it *was* common despite the fact that we should not been doing it
> this way).
> However the comments about avoiding the designer seems another kind of
> difficult pill to swallow. If I am not to use the query designer as crutch
> to have "fun" to build quiers by dragging and dropping my fields and
> dropping in tables, then what am I supposed to use here? In other words I
> love those query designers, and to be honest as a general rule it makes
> sense to use tools that do most of the work for me.
> I have relied on query builders to do most the work for me in just about a
> every development environment that I used sql in (and it been a LOT of
> systems). (SQL, FoxPro, ms-access, enterprise tools, MySql tools, this
> list is RATHER long).
> It just seems to me that building SQL by hand is like going back to
> developing web pages by hand coding your HTML. You might tweak the HTML
> (or sql) by hand, but you likey should start with some type of development
> and building tool here. This is really a cost of human time versus machine
> computer time.
> I now kind of wonder what direction most developers are taking when they
> are developing applications with many many SQL queries, and where do most
> people store and place those queries? As I mentioned in the past it was
> common to use those views as a storage mechanism -- and I well know it's
> been rightfully pointed out that views should never have been used as a
> repository for those SQL statements.
> However, typing in freeform sql and saving that sql in some text system
> makes little sense without some tools to aid in the desing and syntax of
> that sql. It is just not productive, and I am not quite sure what
> direction I should take now in this regards. (I suppose with visual studio
> there is linQ, but that's another matter again).
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> pleaseNOOSpamKallal@.msn.com
>
|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:uNaFtdVcIHA.3484@.TK2MSFTNGP06.phx.gbl...
> Hi Albert
> It seems that you code is most likely to have been based on single
> statement queries rather than stored procedures that contain multiple
> statements?
It can go both ways. However, in the case were we need to execute
several sql things, then a stored proc is the way to go.
However, even then, it would be nice to be able to "use" saved sql
statements that one has designed (with a nice query builder) in those
stored procs (or have the client execute that saved sql).I guess I
looking for "where" do people save that sql code if they want to
keep it out of the client side of the application. A stored proc
is one place, but then you don't get the benefits of query designer
for that sql code created *in* the proc...

> You may want to watch a few of the shorts on jumpstarttv.com such a
> Brian's intoduction to management studio
> http://www.jumpstarttv.com/Media.aspx?vid=58 or more generic T-SQL videos
> by Kathi Kellenberger http://www.jumpstarttv.com/Media.aspx?vid=335
>
nice little video. I quite up to speed with the sql tools now.
I only got on "what tools" bandwagon since the other poster seemed to
suggest that one should not bother using the sql studio tools to create sql.
the quote was:

>Personally I would never use the view designer. The fact that it messes
>with
your query syntax and disallows certain valid constructs is reason enough to
avoid the designer altogether IMO.
So, I not sure if that suggestion was the "widely" accepted practice for
most
sql people to avoid the sql builder or not...
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@.msn.com
|||Hi Albert
By their nature the tools and wizards can only take you to a certain level,
I suspect most of the very experienced DBAs would have worked out their own
methods/templates/scripts etc and would not even think about using the
wizards and query designer!
John
"Albert D. Kallal" <PleaseNOOOsPAMmkallal@.msn.com> wrote in message
news:ONzDYRycIHA.4016@.TK2MSFTNGP03.phx.gbl...
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:uNaFtdVcIHA.3484@.TK2MSFTNGP06.phx.gbl...
> It can go both ways. However, in the case were we need to execute
> several sql things, then a stored proc is the way to go.
> However, even then, it would be nice to be able to "use" saved sql
> statements that one has designed (with a nice query builder) in those
> stored procs (or have the client execute that saved sql).I guess I
> looking for "where" do people save that sql code if they want to
> keep it out of the client side of the application. A stored proc
> is one place, but then you don't get the benefits of query designer
> for that sql code created *in* the proc...
>
> nice little video. I quite up to speed with the sql tools now.
> I only got on "what tools" bandwagon since the other poster seemed to
> suggest that one should not bother using the sql studio tools to create
> sql.
> the quote was:
> your query syntax and disallows certain valid constructs is reason enough
> to
> avoid the designer altogether IMO.
> So, I not sure if that suggestion was the "widely" accepted practice for
> most
> sql people to avoid the sql builder or not...
>
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> pleaseNOOSpamKallal@.msn.com
>
|||if you do this:
CREATE VIEW DBO.MICROSOFT_ARE_DUMBOS
AS
SELECT TOP 100 PERCENT *
FROM DBO.TABLE
ORDER BY RECORDID
what you actually get is this:
CREATE VIEW DBO.MICROSOFT_ARE_DUMBOS
AS
SELECT TOP 100 PERCENT *
FROM DBO.TABLE
this means that any ordering has to be done a level above the view
which means millions of applications and setups all over the world
are now broken
for no good reason
and things that should never be part of an application (ie: knowledge
of internal table structure)
now have to be
of course you can use a stored procedure instead
BUT CAN YOU?
what about all those applications that link to a SQL view
like Microsoft Access Linked Tables ?
now you have to create a new Access query that has intimate knowledge
of the internal workings of the remote database !!!!
HOLD ON!
and what if the columns used to order the view are not in the select
clause ?
then the Access query HAS NO WAY of ordering the data correctly unless
you also
REWRITE THE SQL QUERIES
this pointless management-commitee-style decision by Muppetsoft is a
disaster for many applications
there are too many people working at microsoft - a big cull is
necessary before the sickness spreads too far
there is a temporary workaround here:
http://support.microsoft.com/kb/926292
but read the article carefully
|||"John Rivers" <first10@.btinternet.com> wrote in message
news:18fd596c-8f7e-40e5-96a3-0c41124cb941@.62g2000hsn.googlegroups.com...
> if you do this:
> CREATE VIEW DBO.MICROSOFT_ARE_DUMBOS
> AS
> SELECT TOP 100 PERCENT *
> FROM DBO.TABLE
> ORDER BY RECORDID
> what you actually get is this:
> CREATE VIEW DBO.MICROSOFT_ARE_DUMBOS
> AS
> SELECT TOP 100 PERCENT *
> FROM DBO.TABLE
> this means that any ordering has to be done a level above the view
Correct.

> which means millions of applications and setups all over the world
> are now broken
Which is why they should have followed the SQL standard from day one.
In addition, I believe Itzak Ben-Gan has an example of where ORDER BY won't
work correctly in SQL 2000 in the first place.

> for no good reason
Other than the fact that by definition tables don't have order and views are
logically the same thing as tables.

> and things that should never be part of an application (ie: knowledge
> of internal table structure)
This makes no more sense then if you were talking about a table itself.

> now have to be
> of course you can use a stored procedure instead
> BUT CAN YOU?
Yes.

> what about all those applications that link to a SQL view
> like Microsoft Access Linked Tables ?
What about them? They should not and never assumed an order.

> now you have to create a new Access query that has intimate knowledge
> of the internal workings of the remote database !!!!
> HOLD ON!
> and what if the columns used to order the view are not in the select
> clause ?
> then the Access query HAS NO WAY of ordering the data correctly unless
> you also
> REWRITE THE SQL QUERIES
So do it right the first time.

> this pointless management-commitee-style decision by Muppetsoft is a
> disaster for many applications
>
This pointless management-commitee-style (sic) decision by Microsoft brings
it into compliance with ISO SQL definitions.

> there are too many people working at microsoft - a big cull is
> necessary before the sickness spreads too far
And I suggest some people need to read up on database fundamentals.

>
> there is a temporary workaround here:
> http://support.microsoft.com/kb/926292
> but read the article carefully
>
>
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||"John Rivers" <first10@.btinternet.com> wrote in message
news:18fd596c-8f7e-40e5-96a3-0c41124cb941@.62g2000hsn.googlegroups.com...
>.
> there are too many people working at microsoft - a big cull is
> necessary before the sickness spreads too far
>.
Well don't get a culinary
There already was a cull in the sql server group to stop the bleeding. To
MSs chagrin they still must put up with problems in legacy languages
www.beyondsql.blogspot.com

Order by in sql 2005 views = don't use?

I shudder to bring this issue up, but in very simple terms:
Saving a "view" in sql 2005 with a order by clause WILL NOT guarantee any
particular order of data if I in fact query that view via some sql? Is this
correct?
Eg:
Select * from myView
Is my assumption correct? And this is a notable change from sql 2000 in
which the order of a view was respected? Note that I am well aware this
practice in sql 2000 NEVER should have been widely used, but it was (and I
suppose it just been pure luck that views kept their order of data
returned).
I should WELL note that I perfectly understand that a rdbms is a un-ordered
hunk of data. If you need data in some order, you add an order by clause to
the sql request. This is *perfectly* clear, and perfectly normal to me. This
issue of setting order of data comes up a lot in the access newsgroups also.
However, I was NOT aware that a view is ALSO simply considered a table, and
as such an un-order hunk of data also! Does this mean again that the
developer MUST specify the order when retrieving data, *even* from a view,
and EVEN when that view has a order by clause? I also note that views also
"now" require the obligatory top 100 command, and again this requirement
supports the idea that order by in the view does not make sense!
Is the above a correct view (pun intended) that setting a order by clause in
a saved view don't amount to a hill of beans and cannot be relied upon?
I ask the above, because in a lot of vb6 projects that connected to SQL
server 2000, it was very common to use a view as a way to store tons and
tons of SQL statements. In other words this approach was used to simply not
have inline SQL in the application. I should point out that there is a
significant difference in using server views for saving a whole bunch of SQL
statements you hope to execute in the future, as Opposed to that of a
actually needing a real database view. This "view" feature was Obviously
abused in the past.
In many cases we developers simply used those views to store our SQL, and
we're
not really interested in the actual technical details of what a TRUE
database view is supposed to represent. In retrospect, it probably would
have been far more intelligent to develop one's own "sql" data store that
saves the sql for use with code in some data store.
I apologize for the lengthy post, but it seems to me that the conceptual
concept of views used in SQL server 2005 means that we developers should not
use views as a dumping ground in which to save all kinds of SQL select
statements. This was a Common practice in the past, it seems to me now that
this approach should be avoided in the future.
Am I reading this correct? (or can I still risk relying on a view to
return ordered data for me?).
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@.msn.com"Albert D. Kallal" <PleaseNOOOsPAMmkallal@.msn.com> wrote in message
news:eYfl1oIcIHA.1376@.TK2MSFTNGP02.phx.gbl...
>I shudder to bring this issue up, but in very simple terms:
> Saving a "view" in sql 2005 with a order by clause WILL NOT guarantee any
> particular order of data if I in fact query that view via some sql? Is
> this
> correct?
> Eg:
> Select * from myView
>
[snip]
It is correct that the ordering of rows returned by ANY query is undefined
unless that query includes ORDER BY. Obviously the fact that a tabular
result is displayed on the screen means that some kind of ordering is
shown - but you cannot and should not rely on it always being the same. That
is true irrespective of whether the query references tables or views and it
applies equally to all versions: 7.0, 2000, 2005, 2008.
> And this is a notable change from sql 2000 in
> which the order of a view was respected?
It is not a change. There is no such thing as the "order of a view".
> However, I was NOT aware that a view is ALSO simply considered a table,
> and
> as such an un-order hunk of data also! Does this mean again that the
> developer MUST specify the order when retrieving data, *even* from a view,
> and EVEN when that view has a order by clause?
In relational terms a view and a table are one and the same - they are both
relations and relations are unordered sets of tuples.
SQL Server 2000 introduced a very silly syntax for the TOP clause, which
used ORDER BY to specify the criteria used to select a subset of n or n%
rows. Unfortunately this has caused endless confusion and led some people to
assume that ordering of a view was possible - even though Microsoft never
claimed any such thing and even though the concept of an ordered view is
alien to anyone familiar with the fundamentals of SQL or the relational
model.
> I also note that views also
> "now" require the obligatory top 100 command
That is false. There is no such requirement. I expect you are using the view
designer "feature" that does insert TOP 100 PERCENT in some cases.
Personally I would never use the view designer. The fact that it messes with
your query syntax and disallows certain valid constructs is reason enough to
avoid the designer altogether IMO.
There are many previous discussions on this topic, all reiterating much the
same points.
--
David Portas|||In addition to David's notes, SQL Server 2005 Books Online has this
summarized very well in one note (under CREATE VIEW):
"The ORDER BY clause is used only to determine the rows that are returned by
the TOP clause in the view definition. The ORDER BY clause does not
guarantee ordered results when the view is queried, unless ORDER BY is also
specified in the query itself."
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> That is false. There is no such requirement. I expect you are using the
> view designer "feature" that does insert TOP 100 PERCENT in some cases.
> Personally I would never use the view designer. The fact that it messes
> with your query syntax and disallows certain valid constructs is reason
> enough to avoid the designer altogether IMO.
Thank you very much for your comments on this matter.
Ouch!! I understand perfectly your respsone, and agree. However, advoid the
desinger is a ouch for me!!!
As I commemned, many a developers relied on those views to return data in
particular order, and as you commented it should never been done that way
(but, it *was* common despite the fact that we should not been doing it this
way).
However the comments about avoiding the designer seems another kind of
difficult pill to swallow. If I am not to use the query designer as crutch
to have "fun" to build quiers by dragging and dropping my fields and
dropping in tables, then what am I supposed to use here? In other words I
love those query designers, and to be honest as a general rule it makes
sense to use tools that do most of the work for me.
I have relied on query builders to do most the work for me in just about a
every development environment that I used sql in (and it been a LOT of
systems). (SQL, FoxPro, ms-access, enterprise tools, MySql tools, this list
is RATHER long).
It just seems to me that building SQL by hand is like going back to
developing web pages by hand coding your HTML. You might tweak the HTML (or
sql) by hand, but you likey should start with some type of development and
building tool here. This is really a cost of human time versus machine
computer time.
I now kind of wonder what direction most developers are taking when they are
developing applications with many many SQL queries, and where do most people
store and place those queries? As I mentioned in the past it was common to
use those views as a storage mechanism -- and I well know it's been
rightfully pointed out that views should never have been used as a
repository for those SQL statements.
However, typing in freeform sql and saving that sql in some text system
makes little sense without some tools to aid in the desing and syntax of
that sql. It is just not productive, and I am not quite sure what direction
I should take now in this regards. (I suppose with visual studio there is
linQ, but that's another matter again).
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@.msn.com|||Some additional info and viewpoints:
http://sqlblog.com/blogs/tibor_karaszi/archive/2007/11/28/sorted-views.aspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Albert D. Kallal" <PleaseNOOOsPAMmkallal@.msn.com> wrote in message
news:eYfl1oIcIHA.1376@.TK2MSFTNGP02.phx.gbl...
>I shudder to bring this issue up, but in very simple terms:
> Saving a "view" in sql 2005 with a order by clause WILL NOT guarantee any
> particular order of data if I in fact query that view via some sql? Is this
> correct?
> Eg:
> Select * from myView
> Is my assumption correct? And this is a notable change from sql 2000 in
> which the order of a view was respected? Note that I am well aware this
> practice in sql 2000 NEVER should have been widely used, but it was (and I
> suppose it just been pure luck that views kept their order of data
> returned).
> I should WELL note that I perfectly understand that a rdbms is a un-ordered
> hunk of data. If you need data in some order, you add an order by clause to
> the sql request. This is *perfectly* clear, and perfectly normal to me. This
> issue of setting order of data comes up a lot in the access newsgroups also.
> However, I was NOT aware that a view is ALSO simply considered a table, and
> as such an un-order hunk of data also! Does this mean again that the
> developer MUST specify the order when retrieving data, *even* from a view,
> and EVEN when that view has a order by clause? I also note that views also
> "now" require the obligatory top 100 command, and again this requirement
> supports the idea that order by in the view does not make sense!
> Is the above a correct view (pun intended) that setting a order by clause in
> a saved view don't amount to a hill of beans and cannot be relied upon?
> I ask the above, because in a lot of vb6 projects that connected to SQL
> server 2000, it was very common to use a view as a way to store tons and
> tons of SQL statements. In other words this approach was used to simply not
> have inline SQL in the application. I should point out that there is a
> significant difference in using server views for saving a whole bunch of SQL
> statements you hope to execute in the future, as Opposed to that of a
> actually needing a real database view. This "view" feature was Obviously
> abused in the past.
> In many cases we developers simply used those views to store our SQL, and we're
> not really interested in the actual technical details of what a TRUE
> database view is supposed to represent. In retrospect, it probably would
> have been far more intelligent to develop one's own "sql" data store that
> saves the sql for use with code in some data store.
> I apologize for the lengthy post, but it seems to me that the conceptual
> concept of views used in SQL server 2005 means that we developers should not
> use views as a dumping ground in which to save all kinds of SQL select
> statements. This was a Common practice in the past, it seems to me now that
> this approach should be avoided in the future.
> Am I reading this correct? (or can I still risk relying on a view to
> return ordered data for me?).
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> pleaseNOOSpamKallal@.msn.com
>|||Hi Albert
It seems that you code is most likely to have been based on single statement
queries rather than stored procedures that contain multiple statements?
You should find that after a while using the query window or some other tool
that you wonder why you ever used the query builder, expecially as there are
templates and the drag and drop features of the object explorer (where you
can pull database/tables/column names across into the window.
You may want to watch a few of the shorts on jumpstarttv.com such a Brian's
intoduction to management studio
http://www.jumpstarttv.com/Media.aspx?vid=58 or more generic T-SQL videos by
Kathi Kellenberger http://www.jumpstarttv.com/Media.aspx?vid=335
John
"Albert D. Kallal" <PleaseNOOOsPAMmkallal@.msn.com> wrote in message
news:u7tNbiOcIHA.1376@.TK2MSFTNGP02.phx.gbl...
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
>
>> That is false. There is no such requirement. I expect you are using the
>> view designer "feature" that does insert TOP 100 PERCENT in some cases.
>> Personally I would never use the view designer. The fact that it messes
>> with your query syntax and disallows certain valid constructs is reason
>> enough to avoid the designer altogether IMO.
> Thank you very much for your comments on this matter.
> Ouch!! I understand perfectly your respsone, and agree. However, advoid
> the desinger is a ouch for me!!!
> As I commemned, many a developers relied on those views to return data in
> particular order, and as you commented it should never been done that way
> (but, it *was* common despite the fact that we should not been doing it
> this way).
> However the comments about avoiding the designer seems another kind of
> difficult pill to swallow. If I am not to use the query designer as crutch
> to have "fun" to build quiers by dragging and dropping my fields and
> dropping in tables, then what am I supposed to use here? In other words I
> love those query designers, and to be honest as a general rule it makes
> sense to use tools that do most of the work for me.
> I have relied on query builders to do most the work for me in just about a
> every development environment that I used sql in (and it been a LOT of
> systems). (SQL, FoxPro, ms-access, enterprise tools, MySql tools, this
> list is RATHER long).
> It just seems to me that building SQL by hand is like going back to
> developing web pages by hand coding your HTML. You might tweak the HTML
> (or sql) by hand, but you likey should start with some type of development
> and building tool here. This is really a cost of human time versus machine
> computer time.
> I now kind of wonder what direction most developers are taking when they
> are developing applications with many many SQL queries, and where do most
> people store and place those queries? As I mentioned in the past it was
> common to use those views as a storage mechanism -- and I well know it's
> been rightfully pointed out that views should never have been used as a
> repository for those SQL statements.
> However, typing in freeform sql and saving that sql in some text system
> makes little sense without some tools to aid in the desing and syntax of
> that sql. It is just not productive, and I am not quite sure what
> direction I should take now in this regards. (I suppose with visual studio
> there is linQ, but that's another matter again).
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> pleaseNOOSpamKallal@.msn.com
>|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:uNaFtdVcIHA.3484@.TK2MSFTNGP06.phx.gbl...
> Hi Albert
> It seems that you code is most likely to have been based on single
> statement queries rather than stored procedures that contain multiple
> statements?
It can go both ways. However, in the case were we need to execute
several sql things, then a stored proc is the way to go.
However, even then, it would be nice to be able to "use" saved sql
statements that one has designed (with a nice query builder) in those
stored procs (or have the client execute that saved sql).I guess I
looking for "where" do people save that sql code if they want to
keep it out of the client side of the application. A stored proc
is one place, but then you don't get the benefits of query designer
for that sql code created *in* the proc...
> You may want to watch a few of the shorts on jumpstarttv.com such a
> Brian's intoduction to management studio
> http://www.jumpstarttv.com/Media.aspx?vid=58 or more generic T-SQL videos
> by Kathi Kellenberger http://www.jumpstarttv.com/Media.aspx?vid=335
>
nice little video. I quite up to speed with the sql tools now.
I only got on "what tools" bandwagon since the other poster seemed to
suggest that one should not bother using the sql studio tools to create sql.
the quote was:
>Personally I would never use the view designer. The fact that it messes
>with
your query syntax and disallows certain valid constructs is reason enough to
avoid the designer altogether IMO.
So, I not sure if that suggestion was the "widely" accepted practice for
most
sql people to avoid the sql builder or not...
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@.msn.com|||Hi Albert
By their nature the tools and wizards can only take you to a certain level,
I suspect most of the very experienced DBAs would have worked out their own
methods/templates/scripts etc and would not even think about using the
wizards and query designer!
John
"Albert D. Kallal" <PleaseNOOOsPAMmkallal@.msn.com> wrote in message
news:ONzDYRycIHA.4016@.TK2MSFTNGP03.phx.gbl...
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:uNaFtdVcIHA.3484@.TK2MSFTNGP06.phx.gbl...
>> Hi Albert
>> It seems that you code is most likely to have been based on single
>> statement queries rather than stored procedures that contain multiple
>> statements?
> It can go both ways. However, in the case were we need to execute
> several sql things, then a stored proc is the way to go.
> However, even then, it would be nice to be able to "use" saved sql
> statements that one has designed (with a nice query builder) in those
> stored procs (or have the client execute that saved sql).I guess I
> looking for "where" do people save that sql code if they want to
> keep it out of the client side of the application. A stored proc
> is one place, but then you don't get the benefits of query designer
> for that sql code created *in* the proc...
>> You may want to watch a few of the shorts on jumpstarttv.com such a
>> Brian's intoduction to management studio
>> http://www.jumpstarttv.com/Media.aspx?vid=58 or more generic T-SQL videos
>> by Kathi Kellenberger http://www.jumpstarttv.com/Media.aspx?vid=335
> nice little video. I quite up to speed with the sql tools now.
> I only got on "what tools" bandwagon since the other poster seemed to
> suggest that one should not bother using the sql studio tools to create
> sql.
> the quote was:
>>Personally I would never use the view designer. The fact that it messes
>>with
> your query syntax and disallows certain valid constructs is reason enough
> to
> avoid the designer altogether IMO.
> So, I not sure if that suggestion was the "widely" accepted practice for
> most
> sql people to avoid the sql builder or not...
>
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> pleaseNOOSpamKallal@.msn.com
>|||if you do this:
CREATE VIEW DBO.MICROSOFT_ARE_DUMBOS
AS
SELECT TOP 100 PERCENT *
FROM DBO.TABLE
ORDER BY RECORDID
what you actually get is this:
CREATE VIEW DBO.MICROSOFT_ARE_DUMBOS
AS
SELECT TOP 100 PERCENT *
FROM DBO.TABLE
this means that any ordering has to be done a level above the view
which means millions of applications and setups all over the world
are now broken
for no good reason
and things that should never be part of an application (ie: knowledge
of internal table structure)
now have to be
of course you can use a stored procedure instead
BUT CAN YOU'
what about all those applications that link to a SQL view
like Microsoft Access Linked Tables ?
now you have to create a new Access query that has intimate knowledge
of the internal workings of the remote database !!!!
HOLD ON!
and what if the columns used to order the view are not in the select
clause ?
then the Access query HAS NO WAY of ordering the data correctly unless
you also
REWRITE THE SQL QUERIES
this pointless management-commitee-style decision by Muppetsoft is a
disaster for many applications
there are too many people working at microsoft - a big cull is
necessary before the sickness spreads too far
there is a temporary workaround here:
http://support.microsoft.com/kb/926292
but read the article carefully|||"John Rivers" <first10@.btinternet.com> wrote in message
news:18fd596c-8f7e-40e5-96a3-0c41124cb941@.62g2000hsn.googlegroups.com...
> if you do this:
> CREATE VIEW DBO.MICROSOFT_ARE_DUMBOS
> AS
> SELECT TOP 100 PERCENT *
> FROM DBO.TABLE
> ORDER BY RECORDID
> what you actually get is this:
> CREATE VIEW DBO.MICROSOFT_ARE_DUMBOS
> AS
> SELECT TOP 100 PERCENT *
> FROM DBO.TABLE
> this means that any ordering has to be done a level above the view
Correct.
> which means millions of applications and setups all over the world
> are now broken
Which is why they should have followed the SQL standard from day one.
In addition, I believe Itzak Ben-Gan has an example of where ORDER BY won't
work correctly in SQL 2000 in the first place.
> for no good reason
Other than the fact that by definition tables don't have order and views are
logically the same thing as tables.
> and things that should never be part of an application (ie: knowledge
> of internal table structure)
This makes no more sense then if you were talking about a table itself.
> now have to be
> of course you can use a stored procedure instead
> BUT CAN YOU'
Yes.
> what about all those applications that link to a SQL view
> like Microsoft Access Linked Tables ?
What about them? They should not and never assumed an order.
> now you have to create a new Access query that has intimate knowledge
> of the internal workings of the remote database !!!!
> HOLD ON!
> and what if the columns used to order the view are not in the select
> clause ?
> then the Access query HAS NO WAY of ordering the data correctly unless
> you also
> REWRITE THE SQL QUERIES
So do it right the first time.
> this pointless management-commitee-style decision by Muppetsoft is a
> disaster for many applications
>
This pointless management-commitee-style (sic) decision by Microsoft brings
it into compliance with ISO SQL definitions.
> there are too many people working at microsoft - a big cull is
> necessary before the sickness spreads too far
And I suggest some people need to read up on database fundamentals.
>
> there is a temporary workaround here:
> http://support.microsoft.com/kb/926292
> but read the article carefully
>
>
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||"John Rivers" <first10@.btinternet.com> wrote in message
news:18fd596c-8f7e-40e5-96a3-0c41124cb941@.62g2000hsn.googlegroups.com...
>.
> there are too many people working at microsoft - a big cull is
> necessary before the sickness spreads too far
>.
Well don't get a culinary:)
There already was a cull in the sql server group to stop the bleeding. To
MSs chagrin they still must put up with problems in legacy languages :)
www.beyondsql.blogspot.com|||"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:%23UxTeLBdIHA.3400@.TK2MSFTNGP03.phx.gbl...
> "John Rivers" <first10@.btinternet.com> wrote in message
> news:18fd596c-8f7e-40e5-96a3-0c41124cb941@.62g2000hsn.googlegroups.com...
>.
> And I suggest some people need to read up on database fundamentals.
>
That is both sound and perverse advice. Sorta like water, water everywhere
and not a drop to drink:)
www.beyondsql.blogspot.com

Order By computed columns

Hi all,
I have a long runing query took 70s and returns only 124 rows. I found the
problem is that it uses a compute column in the Order By clause. Something
like this
SELECT ... ORDER BY ISNULL(Table1.Field1, '') + '|' +
ISNULL(CONVERT(nvarchar, Table2.Field2), '''')
If I took that Order By away, it only takes 2s. (That make me think my C#
client code can sort better than that :P )
Can anyone show me what are the ways I can do to optimize it?
Any thoughts are appreciated.
ConradConrad Chan wrote:
> Hi all,
> I have a long runing query took 70s and returns only 124 rows. I found th
e
> problem is that it uses a compute column in the Order By clause. Somethin
g
> like this
> SELECT ... ORDER BY ISNULL(Table1.Field1, '') + '|' +
> ISNULL(CONVERT(nvarchar, Table2.Field2), '''')
> If I took that Order By away, it only takes 2s. (That make me think my C#
> client code can sort better than that :P )
> Can anyone show me what are the ways I can do to optimize it?
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
If that expression is in the SELECT clause you can use
ORDER BY n
where n is the ordinal number of the expression in the SELECT clause.
E.g.:
SELECT col1, col2, (col4 * 0.25) / 100, ...
FROM ...
ORDER BY 3
Will sort the resultset by the value of the 3rd column in the SELECT
clause.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQjdSGYechKqOuFEgEQKsQgCggvSDYuQCwIcw
DXSdEtuVA3YD+b4AnixS
BnAeboIAn+Ja2WD/GUp486uA
=1vFd
--END PGP SIGNATURE--|||If you can do it in the client side, then do it.
AMB
"Conrad Chan" wrote:

> Hi all,
> I have a long runing query took 70s and returns only 124 rows. I found th
e
> problem is that it uses a compute column in the Order By clause. Somethin
g
> like this
> SELECT ... ORDER BY ISNULL(Table1.Field1, '') + '|' +
> ISNULL(CONVERT(nvarchar, Table2.Field2), '''')
> If I took that Order By away, it only takes 2s. (That make me think my C#
> client code can sort better than that :P )
> Can anyone show me what are the ways I can do to optimize it?
> Any thoughts are appreciated.
> Conrad|||I will say only if db really cannot do a better job.
Thanks
Conrad
"Alejandro Mesa" wrote:
> If you can do it in the client side, then do it.
>
> AMB
> "Conrad Chan" wrote:
>|||Unfortunately it doesn't make a difference :<
Conrad
"MGFoster" wrote:

> Conrad Chan wrote:
> --BEGIN PGP SIGNED MESSAGE--
> Hash: SHA1
> If that expression is in the SELECT clause you can use
> ORDER BY n
> where n is the ordinal number of the expression in the SELECT clause.
> E.g.:
> SELECT col1, col2, (col4 * 0.25) / 100, ...
> FROM ...
> ORDER BY 3
> Will sort the resultset by the value of the 3rd column in the SELECT
> clause.
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
> --BEGIN PGP SIGNATURE--
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
> iQA/ AwUBQjdSGYechKqOuFEgEQKsQgCggvSDYuQCwIcw
DXSdEtuVA3YD+b4AnixS
> BnAeboIAn+Ja2WD/GUp486uA
> =1vFd
> --END PGP SIGNATURE--
>|||You can simplify the ORDER BY clause to
ORDER BY COALESCE(Table1.Field1, ''), COALESCE(CAST(Table2.Field2 AS
nvarchar), '''')
or even to
ORDER BY Table1.Field1, Table2.Field2
HTH,
Gert-Jan
Conrad Chan wrote:
> Hi all,
> I have a long runing query took 70s and returns only 124 rows. I found th
e
> problem is that it uses a compute column in the Order By clause. Somethin
g
> like this
> SELECT ... ORDER BY ISNULL(Table1.Field1, '') + '|' +
> ISNULL(CONVERT(nvarchar, Table2.Field2), '''')
> If I took that Order By away, it only takes 2s. (That make me think my C#
> client code can sort better than that :P )
> Can anyone show me what are the ways I can do to optimize it?
> Any thoughts are appreciated.
> Conrad|||Conrad,
In General it should never take SQL Server 68 seconds to sort 124
records... SOmething else is going on here ... Run the query in Query
Analyzer with ShowPlan ON, and see what step in the showplan is taking that
long...
"Conrad Chan" wrote:

> Hi all,
> I have a long runing query took 70s and returns only 124 rows. I found th
e
> problem is that it uses a compute column in the Order By clause. Somethin
g
> like this
> SELECT ... ORDER BY ISNULL(Table1.Field1, '') + '|' +
> ISNULL(CONVERT(nvarchar, Table2.Field2), '''')
> If I took that Order By away, it only takes 2s. (That make me think my C#
> client code can sort better than that :P )
> Can anyone show me what are the ways I can do to optimize it?
> Any thoughts are appreciated.
> Conrad|||Thanks CBretana,
I did look into query analyzer. 90% is done on the Sort. The only thing I
found may be interested is that the estimated row count is 15,000 compared
with 124 row count.
Conrad
"CBretana" wrote:
> Conrad,
> In General it should never take SQL Server 68 seconds to sort 124
> records... SOmething else is going on here ... Run the query in Query
> Analyzer with ShowPlan ON, and see what step in the showplan is taking tha
t
> long...
>
> "Conrad Chan" wrote:
>|||Then you have a filter in the query somewhere, which is reducing the output
from 15,000 to 124, and the sort is happening on the entire 15k recordset,
not the final 124... Suggestion
Rewrite the query as
Select <Stuff>
From (SubSquery: Select Stuff
From <Tables>
Where <Here goes filter predicate tha treduces 15k - 124)
Order By <Order by Clause>
Then inner query must process the filter and deliver the 124 records to the
outer part, where the Order By is...
See if that works...
"Conrad Chan" wrote:
> Thanks CBretana,
> I did look into query analyzer. 90% is done on the Sort. The only thing
I
> found may be interested is that the estimated row count is 15,000 compared
> with 124 row count.
> Conrad
> "CBretana" wrote:
>|||No luck. I simply try to do exactly like you suggest. SQL is smart enough
to realize they are the same. (It is too smart to be stupid)
However, for testing purpose, if I put a TOP inside my sub-select it does
return in 2s.
SELECT * FROM (
SELECT TOP 124 * FROM ...
) ORDER BY 1, 4
Conrad
"CBretana" wrote:
> Then you have a filter in the query somewhere, which is reducing the outpu
t
> from 15,000 to 124, and the sort is happening on the entire 15k recordset,
> not the final 124... Suggestion
> Rewrite the query as
> Select <Stuff>
> From (SubSquery: Select Stuff
> From <Tables>
> Where <Here goes filter predicate tha treduces 15k - 124)
> Order By <Order by Clause>
> Then inner query must process the filter and deliver the 124 records to th
e
> outer part, where the Order By is...
> See if that works...
> "Conrad Chan" wrote:
>sql

ORDER BY columns in SELECT list?

According to BOL, columns in an ORDER BY clause do not have to be in the SELECT
column list unless the SELECT includes DISTINCT, or the UNION operator.

Is this a SQL Server thing, or SQL standard behavior? That is, if I were to write
absolutely pure SQL-92, must columns in the ORDER BY clause be present in the SELECT
list?SQL-92 demands the columns specified in the ORDER BY clause be present in
the SELECT list. I guess the recent standards (99, 03 etc) does not have
this requirement.

--
- Anith
( Please reply to newsgroups only )

Order by column alias

I'm using SQL Server 2005 and are having some troubble with sorting a paged result set. I'm using the OVER Clause to achieve the sorting and paging and have the following query:

1WITH ProjectListAS2(3SELECT4Id,5Name,6Created,7(SELECTCOUNT(*)FROM UserProjectsWHERE ProjectId = p.Id)AS NumberOfUsers,8 ROW_NUMBER()OVER (ORDER BY Id)AS'RowNumber'9FROM Projects p10)11SELECT *12FROM ProjectList13WHERE RowNumberBETWEEN 50AND 60;

This works fine, and give me the results i want. The problem occurs when I want to sort by "NumberOfUsers" which is the results of a sub query.
When i say "ORDER BY NumberOfUsers" instead of Id on line 8, I get the following error:

Msg 207, Level 16, State 1, Line 10
Invalid column name 'NumberOfUsers'.

I read this in the documentation:

When used in the context of a ranking window function, <ORDER BY Clause> can only refer to columns made available by the FROM clause. An integer cannot be specifiedto represent the position of the name or alias of a column in the select list. <ORDER BY Clause> cannot be used with aggregate window functions.

So this means that what I'm trying to do is not possible. How can I then sort by NumberOfUsers? Is there any other way to achieve this

Hi i am not 100% sure if its work but try

select * from ProjectList where RowNumber between 50 and 60

order by 4

|||

Maybe you can try something like this with another inner query?

WITH ProjectListAS
(
Select RR.*,
ROW_NUMBER()OVER (ORDER BY NumberOfUsers)AS'RowNumber'
from (SELECT
Id,
Name,
Created,
(SELECTCOUNT(*)FROM UserProjectsWHERE ProjectId = p.Id)AS NumberOfUsers,
FROM Projects p) RR
)
SELECT *
FROM ProjectList
WHERE RowNumberBETWEEN 50AND 60;

|||

"ORDER BY 4" apparently does not work with windowed functions. This is the error message i got:

Msg 5308, Level 16, State 1, Line 1

Windowed functions do not support integer indices as ORDER BY clause expressions.

|||

jpazgier:

Maybe you can try something like this with another inner query?

WITH ProjectListAS
(
Select RR.*,
ROW_NUMBER()OVER (ORDER BY NumberOfUsers)AS'RowNumber'
from (SELECT
Id,
Name,
Created,
(SELECTCOUNT(*)FROM UserProjectsWHERE ProjectId = p.Id)AS NumberOfUsers,
FROM Projects p) RR
)
SELECT *
FROM ProjectList
WHERE RowNumberBETWEEN 50AND 60;

Works like a charm! Thank you :)

Order by clause work incorrect

when i try the following SQL batch, I get a result-set which is not order by
datetime column 'out_date',but if I delete clause INTO #fifo_temp, I get a correct result with correct order.

who can help me?thanks in advance
...
select tag,stuff_id,stuff_name,cast(out_id as char(10)) as out_id,out_number,out_date,out_qty,remark
INTO #fifo_temp from ##stuff_fifo UNION
select tag,stuff_id,stuff_name,out_id,null,out_date,quant ity,remark
from acc_cost.dbo.stuff_out where tag='A' and left(out_id,3) in ('XSA','TAP')
ORDER BY out_date

DROP TABLE ##stuff_fifo
select * from #fifo_temp

the following can get a correct result:

select tag,stuff_id,stuff_name,cast(out_id as char(10)) as out_id,out_number,out_date,out_qty,remark
from ##stuff_fifo UNION
select tag,stuff_id,stuff_name,out_id,null,out_date,quant ity,remark
from acc_cost.dbo.stuff_out where tag='A' and left(out_id,3) in ('XSA','TAP')
ORDER BY out_dateIf I am not mistaken, you have no influence on the physical order of recordsets saved in tables in MSSQL, so your table #fifo_temp will not be ordered by out_date.

Choose the order of recordsets when extracting the data from the table, so use:

select tag,stuff_id,stuff_name,cast(out_id as char(10)) as out_id,out_number,out_date,out_qty,remark
INTO #fifo_temp from ##stuff_fifo UNION
select tag,stuff_id,stuff_name,out_id,null,out_date,quant ity,remark
from acc_cost.dbo.stuff_out where tag='A' and left(out_id,3) in ('XSA','TAP')

DROP TABLE ##stuff_fifo
select * from #fifo_temp
ORDER BY out_date

Regards

kbk|||KBK is correct. A table has no inherant "order" for either columns or rows, although a result set has order for both. The only place where order makes any sense (or difference) is in the result set.

-PatP|||Set Out_Date as your clustered index and the data will be ordered the way you want, but if the order is important to you then it is best to specify it each time you select from the dataset using and ORDER BY clause.|||I see the problem.Thank all kindly friends.

ORDER BY clause with unknown column name

can i use an unknown column in an ORDER BY clause with t-sql?
i know it will always be an identity field and it is in the first column.
it is also the primary key.
can i depend on a recordset always being in this order without the the
clause?> can i use an unknown column in an ORDER BY clause with t-sql?
> i know it will always be an identity field and it is in the first column.
> it is also the primary key.
> can i depend on a recordset always being in this order without the the
> clause?
Without the what clause?
Are you using SELECT *? Why? This is a preferably avoidable technique in
production code.
You can try using the constant 1, e.g.
SELECT column1, column2, column3
FROM dbo.Table
ORDER BY 1;
This will order by the first column, usually, but it can cause you problems
later, e.g. compare these:
SELECT
[2] = 'b',
[1] = 'a'
UNION
SELECT
[2] = 'a',
[1] = 'b'
ORDER BY 1;
SELECT
[2] = 'b',
[1] = 'a'
UNION
SELECT
[2] = 'a',
[1] = 'b'
ORDER BY [1];
(There are also some other funny rules and bugs I've seen by using constants
in ORDER BY, I can dig them up if need be... I think Steve Kass has posted a
few here.)
Also, if you are using SELECT * (did I mention this was terrible programming
practice and opens a can of barracudas?), can you really rely on your
co-workers to never change the column structure (either intentionally or
accidentally)?
It is trivial to generate a column list, either up front or on the fly, for
any table you are selecting from (especially if you only have to do it once,
e.g. when you create the view or procedure). So I'm not sure I believe that
you will be gaining anything by using * and not having to know the first
column name, because there are a lot of downsides.|||>> can i use an unknown column in an ORDER BY clause with t-sql?
There is no such thing as an unknown column in t-SQL. Use either a column
name or an alias or expression ( with certain limitations ) in the ORDER BY
clause to sort the data the way you want.
Disregarding the visual representation, to sort by the default identity
column in the table you can use:
ORDER BY $IDENTITY
Note that is is only applicable in SQL 2005.
No, you should not depend on any kind of ordering unless you explicitly
included the ORDER BY clause.
Anith|||In addition to what Aaron said, you have no guarantee that the column "n",
where n is the ordinal position of a column instead of a name, is the column
you actually want to order by. If the table were changed, columns added or
removed, or indexes altered, you could end up with a dog of a query trying
to order by column number.
"mcnewsxp" <mcourter@.mindspring.com> wrote in message
news:OvTSanVkGHA.4660@.TK2MSFTNGP05.phx.gbl...
> can i use an unknown column in an ORDER BY clause with t-sql?
> i know it will always be an identity field and it is in the first column.
> it is also the primary key.
> can i depend on a recordset always being in this order without the the
> clause?
>|||order by 1 should do the trick then.
thanks for the warings.
BTW - the column name is known it is just different in different tables.
i inherited what i have and don't want to cahnge too many things because i
have to submit scripts to the DBAs that have to be applied to a couple of
different DBs. just lazy i guess.
thanks much.|||>> can i use an unknown column in an ORDER BY clause with t-sql? <<
No, you have to sort by something. When do not put the ORDER BY in a
cursor (it is not part of a SELECT, another common newbie assumption),
then the engine can out the rows into a sequence in any order. Every
SQL product will be a bit different, depending on physical storage,
parallelism in the hardware, etc.
You might want to read a book and find out why IDENTITY can *never* be
a key. By definition. What you are doing is mimicing a 1950's magnetic
tape file in SQL. The IDENTITY is an exposed physical locator you are
using, the same way we used record positions on a mag tape.
No. This is the definition of a table -- it is a set without any
physical ordering. When you finally read a book on RDBMS, pay
attention to "The Information Prinicple" and some of the other rules
that Dr. Codd set up.
There are some proprietary kludges you can use to destroy portability
and data integrity. For example, there is a ordinal position number
that was removed from Standard SQL a few years ago, but exists in some
products.
All you will get in Newsgroups are the kludges; you need to get an
education. And it will take you at least a year to do that. Your
whole mindset is wrong and you have to unlearn a lot.|||I think the ORDER BY using an ordinal is getting deprecated in a future
version, I'm sure I've read it somewhere...
Tony.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uXuBpsVkGHA.3536@.TK2MSFTNGP05.phx.gbl...
> Without the what clause?
> Are you using SELECT *? Why? This is a preferably avoidable technique in
> production code.
> You can try using the constant 1, e.g.
> SELECT column1, column2, column3
> FROM dbo.Table
> ORDER BY 1;
> This will order by the first column, usually, but it can cause you
> problems later, e.g. compare these:
> SELECT
> [2] = 'b',
> [1] = 'a'
> UNION
> SELECT
> [2] = 'a',
> [1] = 'b'
> ORDER BY 1;
> SELECT
> [2] = 'b',
> [1] = 'a'
> UNION
> SELECT
> [2] = 'a',
> [1] = 'b'
> ORDER BY [1];
> (There are also some other funny rules and bugs I've seen by using
> constants in ORDER BY, I can dig them up if need be... I think Steve Kass
> has posted a few here.)
> Also, if you are using SELECT * (did I mention this was terrible
> programming practice and opens a can of barracudas?), can you really rely
> on your co-workers to never change the column structure (either
> intentionally or accidentally)?
> It is trivial to generate a column list, either up front or on the fly,
> for any table you are selecting from (especially if you only have to do it
> once, e.g. when you create the view or procedure). So I'm not sure I
> believe that you will be gaining anything by using * and not having to
> know the first column name, because there are a lot of downsides.
>|||"mcnewsxp" <mcourter@.mindspring.com> wrote in message
news:eaZV4KWkGHA.1600@.TK2MSFTNGP04.phx.gbl...
> just lazy i guess.
Famous last words. Be very careful taking the easy/fast way out.
What saves you 10 minutes now, may very well cost you 10 hours later on.
List out all your columns, and specify in every script exactly which column
name you are ordering by. That way when someone changes a table or view, or
adds a column to your select statement, your code will still work.|||Yes, I think that's another danger, but I must confess I would probably find
some of that in my code were I to perform a formal review of the last 5
years of work. ;-)
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:e9x8m5WkGHA.1260@.TK2MSFTNGP05.phx.gbl...
>I think the ORDER BY using an ordinal is getting deprecated in a future
>version, I'm sure I've read it somewhere...|||> You might want to read a book and find out why IDENTITY can *never* be
> a key. By definition. What you are doing is mimicing a 1950's magnetic
> tape file in SQL. The IDENTITY is an exposed physical locator you are
> using, the same way we used record positions on a mag tape.
It can be a SURROGATE KEY without problem.
And, your definition re Codd and Date's work on surrogates is just plain
wrong as well.

> All you will get in Newsgroups are the kludges; you need to get an
> education. And it will take you at least a year to do that. Your
> whole mindset is wrong and you have to unlearn a lot.
Do you even realise how condesending and arrogant you sound?
You have plenty of weaknesses yourself.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1150477719.742027.253030@.i40g2000cwc.googlegroups.com...
> No, you have to sort by something. When do not put the ORDER BY in a
> cursor (it is not part of a SELECT, another common newbie assumption),
> then the engine can out the rows into a sequence in any order. Every
> SQL product will be a bit different, depending on physical storage,
> parallelism in the hardware, etc.
>
> You might want to read a book and find out why IDENTITY can *never* be
> a key. By definition. What you are doing is mimicing a 1950's magnetic
> tape file in SQL. The IDENTITY is an exposed physical locator you are
> using, the same way we used record positions on a mag tape.
>
> No. This is the definition of a table -- it is a set without any
> physical ordering. When you finally read a book on RDBMS, pay
> attention to "The Information Prinicple" and some of the other rules
> that Dr. Codd set up.
> There are some proprietary kludges you can use to destroy portability
> and data integrity. For example, there is a ordinal position number
> that was removed from Standard SQL a few years ago, but exists in some
> products.
> All you will get in Newsgroups are the kludges; you need to get an
> education. And it will take you at least a year to do that. Your
> whole mindset is wrong and you have to unlearn a lot.
>