Order by, Using a param

How do you allow sorting via one of the params?
I would like to do something like...
declare @.c char(50)
,@.s char(30)
set @.c = 'SomeClient'
set @.s = 'SomeColumn'
select @.s
select *
from MyTable
where rtrim(clientName) = rtrim(@.c)
order by rtrim(@.s)
thanks in advance..
bob.John 3:16 wrote:
> How do you allow sorting via one of the params?
> I would like to do something like...
> declare @.c char(50)
> ,@.s char(30)
> set @.c = 'SomeClient'
> set @.s = 'SomeColumn'
> select @.s
> select *
> from MyTable
> where rtrim(clientName) = rtrim(@.c)
> order by rtrim(@.s)
>
> thanks in advance..
> bob.
>
DECLARE @.ClientName VARCHAR(50)
DECLARE @.ColumnName VARCHAR(30)
DECLARE @.Command VARCHAR(1024)
SELECT @.ClientName = 'SomeClient'
SELECT @.ColumnName = 'SomeColumn'
SELECT @.Command = 'SELECT * FROM MyTable WHERE clientName = @.ClientName
ORDER BY ' + @.ColumnName
EXEC (@.Command)|||http://www.aspfaq.com/2501
"John 3:16" <bobmcc@.tricoequipment.com> wrote in message
news:egtoAL7kGHA.4816@.TK2MSFTNGP05.phx.gbl...
> How do you allow sorting via one of the params?
> I would like to do something like...
> declare @.c char(50)
> ,@.s char(30)
> set @.c = 'SomeClient'
> set @.s = 'SomeColumn'
> select @.s
> select *
> from MyTable
> where rtrim(clientName) = rtrim(@.c)
> order by rtrim(@.s)
>
> thanks in advance..
> bob.
>|||Thanks Tracy...
I really appreciate it.
Bob.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:%23TIRzN7kGHA.5108@.TK2MSFTNGP02.phx.gbl...
> John 3:16 wrote:
> DECLARE @.ClientName VARCHAR(50)
> DECLARE @.ColumnName VARCHAR(30)
> DECLARE @.Command VARCHAR(1024)
> SELECT @.ClientName = 'SomeClient'
> SELECT @.ColumnName = 'SomeColumn'
> SELECT @.Command = 'SELECT * FROM MyTable WHERE clientName = @.ClientName
> ORDER BY ' + @.ColumnName
> EXEC (@.Command)|||Thanks Aaron...
I checked out the link....
I appreciate the reply and the link.
Bob.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:upqE0N7kGHA.2052@.TK2MSFTNGP04.phx.gbl...
> http://www.aspfaq.com/2501
>
> "John 3:16" <bobmcc@.tricoequipment.com> wrote in message
> news:egtoAL7kGHA.4816@.TK2MSFTNGP05.phx.gbl...
>|||"John 3:16" <bobmcc@.tricoequipment.com> wrote in message
news:es2VKR7kGHA.4044@.TK2MSFTNGP03.phx.gbl...
> Thanks Tracy...
> I really appreciate it.
> Bob.
If your ORDER BY column name is not hard-wired into your script (highly
likely or you wouldn't need to use Dynamic SQL to generate the SELECT
statement), be very careful that you validate the column name thoroughly or
you can leave yourself open to SQL Injection, as in:
SELECT @.ColumnName = '1; TRUNCATE TABLE SomeImportantTable; --'

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 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 not sorting correctly!

My problem is simple but I cannot see a way to get around it.
I am trying to do an ORDER BY on a varchar field, and it keeps putting
"_" (underscore) before "0" (zero).
i.e. I want it to work like
"_" in ascii is 95, "0" in ascii is 48, therefore "0" comes first
WHY IS IT DOING THIS?!!! I am pulling my hair out in frustration.

>From an increasingly balding
Kaz> I am trying to do an ORDER BY on a varchar field, and it keeps putting
> "_" (underscore) before "0" (zero).
What is the column collation? The collation determines sorting and
comparison rules. Consider:
CREATE TABLE dbo.foo
(
col1 varchar(10) COLLATE Latin1_General_CI_AS,
col2 varchar(10) COLLATE Latin1_General_BIN
)
INSERT INTO dbo.foo VALUES('0', '0')
INSERT INTO dbo.foo VALUES('_', '_')
SELECT * FROM dbo.foo
ORDER BY col1
SELECT * FROM dbo.foo
ORDER BY col2
GO
You can also specify an explicit collation in your ORDER BY, although
indexes can't be used to facilitate efficient ordering due to the COLLATE
expression:
SELECT * FROM dbo.foo
ORDER BY col1 COLLATE Latin1_General_BIN
Hope this helps.
Dan Guzman
SQL Server MVP
"kaz" <snow-berry@.animail.net> wrote in message
news:1185417750.022600.219110@.i13g2000prf.googlegroups.com...
> My problem is simple but I cannot see a way to get around it.
> I am trying to do an ORDER BY on a varchar field, and it keeps putting
> "_" (underscore) before "0" (zero).
> i.e. I want it to work like
> "_" in ascii is 95, "0" in ascii is 48, therefore "0" comes first
> WHY IS IT DOING THIS?!!! I am pulling my hair out in frustration.
>
> Kaz
>|||Thank you Dan for your response.
My place of work has two versions of SQL server running on different
machines - SQL Server 2000 and SQL Server 7.00.
This helps with the SQL Server 2000 machines, but won't work on SQL
Server 7.0.
Is there any way to get it sort correctly on SQL Server 7.0?
Karen|||> This helps with the SQL Server 2000 machines, but won't work on SQL
> Server 7.0.
> Is there any way to get it sort correctly on SQL Server 7.0?
Yes, Karen. Collations were introduced in SQL Server 2000. In SQL 7 and
earlier versions, you can CAST are CONVERT to varbinary to get binary
sorting you want:
SELECT * FROM dbo.foo
ORDER BY CAST(col1AS varbinary(10))
Hope this helps.
Dan Guzman
SQL Server MVP
"kaz" <snow-berry@.animail.net> wrote in message
news:1185511629.895148.96300@.e9g2000prf.googlegroups.com...
> Thank you Dan for your response.
> My place of work has two versions of SQL server running on different
> machines - SQL Server 2000 and SQL Server 7.00.
> This helps with the SQL Server 2000 machines, but won't work on SQL
> Server 7.0.
> Is there any way to get it sort correctly on SQL Server 7.0?
> Karen
>|||Thank you SO much Dan, you have saved my sanity (or what little of it
I have left )
Karen|||I'm glad I was able to help.
Dan Guzman
SQL Server MVP
"kaz" <snow-berry@.animail.net> wrote in message
news:1185766768.945039.300220@.x35g2000prf.googlegroups.com...
> Thank you SO much Dan, you have saved my sanity (or what little of it
> I have left )
> Karen
>

ORDER BY clause not sorting correctly!

My problem is simple but I cannot see a way to get around it.
I am trying to do an ORDER BY on a varchar field, and it keeps putting
"_" (underscore) before "0" (zero).
i.e. I want it to work like
"_" in ascii is 95, "0" in ascii is 48, therefore "0" comes first
WHY IS IT DOING THIS?!!! I am pulling my hair out in frustration.

>From an increasingly balding
Kaz
> I am trying to do an ORDER BY on a varchar field, and it keeps putting
> "_" (underscore) before "0" (zero).
What is the column collation? The collation determines sorting and
comparison rules. Consider:
CREATE TABLE dbo.foo
(
col1 varchar(10) COLLATE Latin1_General_CI_AS,
col2 varchar(10) COLLATE Latin1_General_BIN
)
INSERT INTO dbo.foo VALUES('0', '0')
INSERT INTO dbo.foo VALUES('_', '_')
SELECT * FROM dbo.foo
ORDER BY col1
SELECT * FROM dbo.foo
ORDER BY col2
GO
You can also specify an explicit collation in your ORDER BY, although
indexes can't be used to facilitate efficient ordering due to the COLLATE
expression:
SELECT * FROM dbo.foo
ORDER BY col1 COLLATE Latin1_General_BIN
Hope this helps.
Dan Guzman
SQL Server MVP
"kaz" <snow-berry@.animail.net> wrote in message
news:1185417750.022600.219110@.i13g2000prf.googlegr oups.com...
> My problem is simple but I cannot see a way to get around it.
> I am trying to do an ORDER BY on a varchar field, and it keeps putting
> "_" (underscore) before "0" (zero).
> i.e. I want it to work like
> "_" in ascii is 95, "0" in ascii is 48, therefore "0" comes first
> WHY IS IT DOING THIS?!!! I am pulling my hair out in frustration.
> Kaz
>
|||Thank you Dan for your response.
My place of work has two versions of SQL server running on different
machines - SQL Server 2000 and SQL Server 7.00.
This helps with the SQL Server 2000 machines, but won't work on SQL
Server 7.0.
Is there any way to get it sort correctly on SQL Server 7.0?
Karen
|||> This helps with the SQL Server 2000 machines, but won't work on SQL
> Server 7.0.
> Is there any way to get it sort correctly on SQL Server 7.0?
Yes, Karen. Collations were introduced in SQL Server 2000. In SQL 7 and
earlier versions, you can CAST are CONVERT to varbinary to get binary
sorting you want:
SELECT * FROM dbo.foo
ORDER BY CAST(col1AS varbinary(10))
Hope this helps.
Dan Guzman
SQL Server MVP
"kaz" <snow-berry@.animail.net> wrote in message
news:1185511629.895148.96300@.e9g2000prf.googlegrou ps.com...
> Thank you Dan for your response.
> My place of work has two versions of SQL server running on different
> machines - SQL Server 2000 and SQL Server 7.00.
> This helps with the SQL Server 2000 machines, but won't work on SQL
> Server 7.0.
> Is there any way to get it sort correctly on SQL Server 7.0?
> Karen
>
|||Thank you SO much Dan, you have saved my sanity (or what little of it
I have left )
Karen
|||I'm glad I was able to help.
Dan Guzman
SQL Server MVP
"kaz" <snow-berry@.animail.net> wrote in message
news:1185766768.945039.300220@.x35g2000prf.googlegr oups.com...
> Thank you SO much Dan, you have saved my sanity (or what little of it
> I have left )
> Karen
>

ORDER BY clause not sorting correctly!

My problem is simple but I cannot see a way to get around it.
I am trying to do an ORDER BY on a varchar field, and it keeps putting
"_" (underscore) before "0" (zero).
i.e. I want it to work like
"_" in ascii is 95, "0" in ascii is 48, therefore "0" comes first
WHY IS IT DOING THIS?!!! I am pulling my hair out in frustration.
>From an increasingly balding
Kaz> I am trying to do an ORDER BY on a varchar field, and it keeps putting
> "_" (underscore) before "0" (zero).
What is the column collation? The collation determines sorting and
comparison rules. Consider:
CREATE TABLE dbo.foo
(
col1 varchar(10) COLLATE Latin1_General_CI_AS,
col2 varchar(10) COLLATE Latin1_General_BIN
)
INSERT INTO dbo.foo VALUES('0', '0')
INSERT INTO dbo.foo VALUES('_', '_')
SELECT * FROM dbo.foo
ORDER BY col1
SELECT * FROM dbo.foo
ORDER BY col2
GO
You can also specify an explicit collation in your ORDER BY, although
indexes can't be used to facilitate efficient ordering due to the COLLATE
expression:
SELECT * FROM dbo.foo
ORDER BY col1 COLLATE Latin1_General_BIN
--
Hope this helps.
Dan Guzman
SQL Server MVP
"kaz" <snow-berry@.animail.net> wrote in message
news:1185417750.022600.219110@.i13g2000prf.googlegroups.com...
> My problem is simple but I cannot see a way to get around it.
> I am trying to do an ORDER BY on a varchar field, and it keeps putting
> "_" (underscore) before "0" (zero).
> i.e. I want it to work like
> "_" in ascii is 95, "0" in ascii is 48, therefore "0" comes first
> WHY IS IT DOING THIS?!!! I am pulling my hair out in frustration.
>>From an increasingly balding
> Kaz
>|||Thank you Dan for your response.
My place of work has two versions of SQL server running on different
machines - SQL Server 2000 and SQL Server 7.00.
This helps with the SQL Server 2000 machines, but won't work on SQL
Server 7.0.
Is there any way to get it sort correctly on SQL Server 7.0?
Karen|||> This helps with the SQL Server 2000 machines, but won't work on SQL
> Server 7.0.
> Is there any way to get it sort correctly on SQL Server 7.0?
Yes, Karen. Collations were introduced in SQL Server 2000. In SQL 7 and
earlier versions, you can CAST are CONVERT to varbinary to get binary
sorting you want:
SELECT * FROM dbo.foo
ORDER BY CAST(col1AS varbinary(10))
--
Hope this helps.
Dan Guzman
SQL Server MVP
"kaz" <snow-berry@.animail.net> wrote in message
news:1185511629.895148.96300@.e9g2000prf.googlegroups.com...
> Thank you Dan for your response.
> My place of work has two versions of SQL server running on different
> machines - SQL Server 2000 and SQL Server 7.00.
> This helps with the SQL Server 2000 machines, but won't work on SQL
> Server 7.0.
> Is there any way to get it sort correctly on SQL Server 7.0?
> Karen
>|||Thank you SO much Dan, you have saved my sanity (or what little of it
I have left :) )
Karen|||I'm glad I was able to help.
--
Dan Guzman
SQL Server MVP
"kaz" <snow-berry@.animail.net> wrote in message
news:1185766768.945039.300220@.x35g2000prf.googlegroups.com...
> Thank you SO much Dan, you have saved my sanity (or what little of it
> I have left :) )
> Karen
>