Charts: Using Top N but need to show 'Others' as one

Hi all
I'm generating a column chart, lets say its CustomerID as a category
and SUM(Sales.Value) as a values sorted by SUM(Sales.Value) DESC. That
might give 100 column bars so we filter the category to show, say, Top
20.
Can anyone see a way, convoluted or otherwise, of creating one more
column bar to represent SUM(Sales.Value) for the other 80.
I just want to be sure that I'm not missing something before I dive
into writing code that creates a temporary table to report on which is
the only way I can see.
Cheers
RossAFAIK there is no function for "others". Perhaps you can do this on your
query and then flush the result to the report. Raise your hand if you need
assitance with this.
HTH, Jens Süßmeyer
--
http://www.sqlserver2005.de
--
"Ross" <synergy56@.hotmail.com> schrieb im Newsbeitrag
news:6sgt515glf8uvkr8vfqqliv554im1l7pak@.4ax.com...
> Hi all
> I'm generating a column chart, lets say its CustomerID as a category
> and SUM(Sales.Value) as a values sorted by SUM(Sales.Value) DESC. That
> might give 100 column bars so we filter the category to show, say, Top
> 20.
> Can anyone see a way, convoluted or otherwise, of creating one more
> column bar to represent SUM(Sales.Value) for the other 80.
> I just want to be sure that I'm not missing something before I dive
> into writing code that creates a temporary table to report on which is
> the only way I can see.
> Cheers
> Ross
>|||Use two queries and union them together. The first query is your top 10.
The second query uses the first query as a subquery to return anly keys,
then do a NOT IN() against the key selection. This will return all but the
top 10 which you can aggregate into a single valued called "Other"
Basing your your chart on the union of these two queries will give you 11
data points; top 10 and other.
Paul Turley
"Ross" <synergy56@.hotmail.com> wrote in message
news:6sgt515glf8uvkr8vfqqliv554im1l7pak@.4ax.com...
> Hi all
> I'm generating a column chart, lets say its CustomerID as a category
> and SUM(Sales.Value) as a values sorted by SUM(Sales.Value) DESC. That
> might give 100 column bars so we filter the category to show, say, Top
> 20.
> Can anyone see a way, convoluted or otherwise, of creating one more
> column bar to represent SUM(Sales.Value) for the other 80.
> I just want to be sure that I'm not missing something before I dive
> into writing code that creates a temporary table to report on which is
> the only way I can see.
> Cheers
> Ross
>|||I did try that, however i'm storing my 'N' for the top N in a parameter, and
i see no way to use a parameter in a query to limit the rows. Likewise i see
no way to add a ranking number in the table itself. anybody?
"Paul Turley" wrote:
> Use two queries and union them together. The first query is your top 10.
> The second query uses the first query as a subquery to return anly keys,
> then do a NOT IN() against the key selection. This will return all but the
> top 10 which you can aggregate into a single valued called "Other"
> Basing your your chart on the union of these two queries will give you 11
> data points; top 10 and other.
> Paul Turley
>
> "Ross" <synergy56@.hotmail.com> wrote in message
> news:6sgt515glf8uvkr8vfqqliv554im1l7pak@.4ax.com...
> > Hi all
> >
> > I'm generating a column chart, lets say its CustomerID as a category
> > and SUM(Sales.Value) as a values sorted by SUM(Sales.Value) DESC. That
> > might give 100 column bars so we filter the category to show, say, Top
> > 20.
> >
> > Can anyone see a way, convoluted or otherwise, of creating one more
> > column bar to represent SUM(Sales.Value) for the other 80.
> >
> > I just want to be sure that I'm not missing something before I dive
> > into writing code that creates a temporary table to report on which is
> > the only way I can see.
> >
> > Cheers
> > Ross
> >
>
>|||You can define a dynamic TopN filter like this:
Filter expression: = Fields!A.Value
Filter operator: TopN
Filter value: = Parameters!Top.Value
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ard Goossens" <ArdGoossens@.discussions.microsoft.com> wrote in message
news:CE512986-BE7C-4189-BC4B-70165DE734F2@.microsoft.com...
>I did try that, however i'm storing my 'N' for the top N in a parameter,
>and
> i see no way to use a parameter in a query to limit the rows. Likewise i
> see
> no way to add a ranking number in the table itself. anybody?
>
> "Paul Turley" wrote:
>> Use two queries and union them together. The first query is your top 10.
>> The second query uses the first query as a subquery to return anly keys,
>> then do a NOT IN() against the key selection. This will return all but
>> the
>> top 10 which you can aggregate into a single valued called "Other"
>> Basing your your chart on the union of these two queries will give you 11
>> data points; top 10 and other.
>> Paul Turley
>>
>> "Ross" <synergy56@.hotmail.com> wrote in message
>> news:6sgt515glf8uvkr8vfqqliv554im1l7pak@.4ax.com...
>> > Hi all
>> >
>> > I'm generating a column chart, lets say its CustomerID as a category
>> > and SUM(Sales.Value) as a values sorted by SUM(Sales.Value) DESC. That
>> > might give 100 column bars so we filter the category to show, say, Top
>> > 20.
>> >
>> > Can anyone see a way, convoluted or otherwise, of creating one more
>> > column bar to represent SUM(Sales.Value) for the other 80.
>> >
>> > I just want to be sure that I'm not missing something before I dive
>> > into writing code that creates a temporary table to report on which is
>> > the only way I can see.
>> >
>> > Cheers
>> > Ross
>> >
>>|||Ard,
You can use paramters and variables with the SET ROWCOUNT command. You
could also build a string containing your TOP N query and execute dynamic
SQL, but this is not my preference.
Ted
"Ard Goossens" wrote:
> I did try that, however i'm storing my 'N' for the top N in a parameter, and
> i see no way to use a parameter in a query to limit the rows. Likewise i see
> no way to add a ranking number in the table itself. anybody?
> "Paul Turley" wrote:
> > Use two queries and union them together. The first query is your top 10.
> > The second query uses the first query as a subquery to return anly keys,
> > then do a NOT IN() against the key selection. This will return all but the
> > top 10 which you can aggregate into a single valued called "Other"
> >
> > Basing your your chart on the union of these two queries will give you 11
> > data points; top 10 and other.|||Robert
Do you know by any chance how to handle the cases when Top is null? Let's
say you want to give the users the option to select a Top N value or to leave
it null. Thx
"Robert Bruckner [MSFT]" wrote:
> You can define a dynamic TopN filter like this:
> Filter expression: = Fields!A.Value
> Filter operator: TopN
> Filter value: = Parameters!Top.Value
>
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Ard Goossens" <ArdGoossens@.discussions.microsoft.com> wrote in message
> news:CE512986-BE7C-4189-BC4B-70165DE734F2@.microsoft.com...
> >I did try that, however i'm storing my 'N' for the top N in a parameter,
> >and
> > i see no way to use a parameter in a query to limit the rows. Likewise i
> > see
> > no way to add a ranking number in the table itself. anybody?
> >
> >
> >
> > "Paul Turley" wrote:
> >
> >> Use two queries and union them together. The first query is your top 10.
> >> The second query uses the first query as a subquery to return anly keys,
> >> then do a NOT IN() against the key selection. This will return all but
> >> the
> >> top 10 which you can aggregate into a single valued called "Other"
> >>
> >> Basing your your chart on the union of these two queries will give you 11
> >> data points; top 10 and other.
> >>
> >> Paul Turley
> >>
> >>
> >> "Ross" <synergy56@.hotmail.com> wrote in message
> >> news:6sgt515glf8uvkr8vfqqliv554im1l7pak@.4ax.com...
> >> > Hi all
> >> >
> >> > I'm generating a column chart, lets say its CustomerID as a category
> >> > and SUM(Sales.Value) as a values sorted by SUM(Sales.Value) DESC. That
> >> > might give 100 column bars so we filter the category to show, say, Top
> >> > 20.
> >> >
> >> > Can anyone see a way, convoluted or otherwise, of creating one more
> >> > column bar to represent SUM(Sales.Value) for the other 80.
> >> >
> >> > I just want to be sure that I'm not missing something before I dive
> >> > into writing code that creates a temporary table to report on which is
> >> > the only way I can see.
> >> >
> >> > Cheers
> >> > Ross
> >> >
> >>
> >>
> >>
>
>|||There are two different approaches how to deal with this situation:
1. "Fake Parameter" approach:
* Create a "fake" hidden (non-prompted) parameter PSize to calculate the
dataset size. I.e. the parameter default value would be
=CountRows("ChartDataSetName")
* on the chart, the filter expression would look like this:
Filter expression: = Fields!A.Value
Filter operator: TopN
Filter value: = iif( Parameters!Top.Value is Nothing,
Parameters!PSize.Value, Parameters!Top.Value)
2. "Filter with Duplicates" approach:
Filter expression: = iif( Parameters!Top.Value is Nothing, 1,
Fields!A.Value)
Filter operator: TopN
Filter value: = iif( Parameters!Top.Value is Nothing, 1,
Parameters!Top.Value)
Robert M. Bruckner
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Marco" <Marco@.discussions.microsoft.com> wrote in message
news:19869577-684A-4971-A89E-DCA199EC5141@.microsoft.com...
> Robert
> Do you know by any chance how to handle the cases when Top is null? Let's
> say you want to give the users the option to select a Top N value or to
> leave
> it null. Thx
> "Robert Bruckner [MSFT]" wrote:
>> You can define a dynamic TopN filter like this:
>> Filter expression: = Fields!A.Value
>> Filter operator: TopN
>> Filter value: = Parameters!Top.Value
>>
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Ard Goossens" <ArdGoossens@.discussions.microsoft.com> wrote in message
>> news:CE512986-BE7C-4189-BC4B-70165DE734F2@.microsoft.com...
>> >I did try that, however i'm storing my 'N' for the top N in a parameter,
>> >and
>> > i see no way to use a parameter in a query to limit the rows. Likewise
>> > i
>> > see
>> > no way to add a ranking number in the table itself. anybody?
>> >
>> >
>> >
>> > "Paul Turley" wrote:
>> >
>> >> Use two queries and union them together. The first query is your top
>> >> 10.
>> >> The second query uses the first query as a subquery to return anly
>> >> keys,
>> >> then do a NOT IN() against the key selection. This will return all but
>> >> the
>> >> top 10 which you can aggregate into a single valued called "Other"
>> >>
>> >> Basing your your chart on the union of these two queries will give you
>> >> 11
>> >> data points; top 10 and other.
>> >>
>> >> Paul Turley
>> >>
>> >>
>> >> "Ross" <synergy56@.hotmail.com> wrote in message
>> >> news:6sgt515glf8uvkr8vfqqliv554im1l7pak@.4ax.com...
>> >> > Hi all
>> >> >
>> >> > I'm generating a column chart, lets say its CustomerID as a category
>> >> > and SUM(Sales.Value) as a values sorted by SUM(Sales.Value) DESC.
>> >> > That
>> >> > might give 100 column bars so we filter the category to show, say,
>> >> > Top
>> >> > 20.
>> >> >
>> >> > Can anyone see a way, convoluted or otherwise, of creating one more
>> >> > column bar to represent SUM(Sales.Value) for the other 80.
>> >> >
>> >> > I just want to be sure that I'm not missing something before I dive
>> >> > into writing code that creates a temporary table to report on which
>> >> > is
>> >> > the only way I can see.
>> >> >
>> >> > Cheers
>> >> > Ross
>> >> >
>> >>
>> >>
>> >>
>>|||Thanks Robert this is very helpful
"Robert Bruckner [MSFT]" wrote:
> There are two different approaches how to deal with this situation:
> 1. "Fake Parameter" approach:
> * Create a "fake" hidden (non-prompted) parameter PSize to calculate the
> dataset size. I.e. the parameter default value would be
> =CountRows("ChartDataSetName")
> * on the chart, the filter expression would look like this:
> Filter expression: = Fields!A.Value
> Filter operator: TopN
> Filter value: = iif( Parameters!Top.Value is Nothing,
> Parameters!PSize.Value, Parameters!Top.Value)
> 2. "Filter with Duplicates" approach:
> Filter expression: = iif( Parameters!Top.Value is Nothing, 1,
> Fields!A.Value)
> Filter operator: TopN
> Filter value: = iif( Parameters!Top.Value is Nothing, 1,
> Parameters!Top.Value)
>
> --
> Robert M. Bruckner
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
> "Marco" <Marco@.discussions.microsoft.com> wrote in message
> news:19869577-684A-4971-A89E-DCA199EC5141@.microsoft.com...
> > Robert
> >
> > Do you know by any chance how to handle the cases when Top is null? Let's
> > say you want to give the users the option to select a Top N value or to
> > leave
> > it null. Thx
> >
> > "Robert Bruckner [MSFT]" wrote:
> >
> >> You can define a dynamic TopN filter like this:
> >> Filter expression: = Fields!A.Value
> >> Filter operator: TopN
> >> Filter value: = Parameters!Top.Value
> >>
> >>
> >> -- Robert
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >> "Ard Goossens" <ArdGoossens@.discussions.microsoft.com> wrote in message
> >> news:CE512986-BE7C-4189-BC4B-70165DE734F2@.microsoft.com...
> >> >I did try that, however i'm storing my 'N' for the top N in a parameter,
> >> >and
> >> > i see no way to use a parameter in a query to limit the rows. Likewise
> >> > i
> >> > see
> >> > no way to add a ranking number in the table itself. anybody?
> >> >
> >> >
> >> >
> >> > "Paul Turley" wrote:
> >> >
> >> >> Use two queries and union them together. The first query is your top
> >> >> 10.
> >> >> The second query uses the first query as a subquery to return anly
> >> >> keys,
> >> >> then do a NOT IN() against the key selection. This will return all but
> >> >> the
> >> >> top 10 which you can aggregate into a single valued called "Other"
> >> >>
> >> >> Basing your your chart on the union of these two queries will give you
> >> >> 11
> >> >> data points; top 10 and other.
> >> >>
> >> >> Paul Turley
> >> >>
> >> >>
> >> >> "Ross" <synergy56@.hotmail.com> wrote in message
> >> >> news:6sgt515glf8uvkr8vfqqliv554im1l7pak@.4ax.com...
> >> >> > Hi all
> >> >> >
> >> >> > I'm generating a column chart, lets say its CustomerID as a category
> >> >> > and SUM(Sales.Value) as a values sorted by SUM(Sales.Value) DESC.
> >> >> > That
> >> >> > might give 100 column bars so we filter the category to show, say,
> >> >> > Top
> >> >> > 20.
> >> >> >
> >> >> > Can anyone see a way, convoluted or otherwise, of creating one more
> >> >> > column bar to represent SUM(Sales.Value) for the other 80.
> >> >> >
> >> >> > I just want to be sure that I'm not missing something before I dive
> >> >> > into writing code that creates a temporary table to report on which
> >> >> > is
> >> >> > the only way I can see.
> >> >> >
> >> >> > Cheers
> >> >> > Ross
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>

Character greater than 'z'

I've searched and have not found any good answers to this. Maybe there
isn't one...
I need to have a string that when sorted with an ORDER BY, it comes after
the letter 'z'.
The database is created with the collation SQL_Latin1_General_CP1_CI_AS.
I know it is best to not rely on this and use some sort of surrogate field
to order the rows, but I cannot do that in this case - the data is being
used by SharePoint (MOSS) in a Business Data Catalog (BDC) column.
SharePoint will sort one of it's lists by the BDC column value, but it sorts
only by the values in that column.
In ASCII, I could use the tilde (~), but SQL is putting the tilde first in
the results.
Is there any character I could put at the beginning of the string so it
would fall out last in the query results?
(Remember, it is SharePoint generating the query, so I cannot change the SQL
it uses. I only control the values in the column.)
I assume the column is char or varchar? If so, you can use the value
CHAR(208), for example,
Set NoCount ON
Create Table #FooBar(Foo varchar(10))
Insert #FooBar (Foo) Values ('A')
Insert #FooBar (Foo) Values (Char(208))
Insert #FooBar (Foo) Values ('z')
Select Foo, Ascii(Foo)
From #FooBar
Order By Foo
go
Drop Table #FooBar
You can find all the characters which will sort after 'z' with the following
code
Set NoCount ON
Create Table #FooBar(Foo varchar(10))
Insert #FooBar (Foo) Values ('A')
Insert #FooBar (Foo) Values ('z')
Declare @.i int
Set @.i = 1
While @.i < 256
Begin
Insert #FooBar (Foo) Values (Char(@.i))
Set @.i = @.i + 1
End
Select Foo, Ascii(Foo)
From #FooBar
Where Foo > 'z'
Order By Foo
go
Drop Table #FooBar
Tom
"JD" <yazoo@.newsgroup.nospam> wrote in message
news:%23uiqZB3nIHA.3556@.TK2MSFTNGP04.phx.gbl...
> I've searched and have not found any good answers to this. Maybe there
> isn't one...
> I need to have a string that when sorted with an ORDER BY, it comes after
> the letter 'z'.
> The database is created with the collation SQL_Latin1_General_CP1_CI_AS.
> I know it is best to not rely on this and use some sort of surrogate field
> to order the rows, but I cannot do that in this case - the data is being
> used by SharePoint (MOSS) in a Business Data Catalog (BDC) column.
> SharePoint will sort one of it's lists by the BDC column value, but it
> sorts only by the values in that column.
> In ASCII, I could use the tilde (~), but SQL is putting the tilde first in
> the results.
> Is there any character I could put at the beginning of the string so it
> would fall out last in the query results?
> (Remember, it is SharePoint generating the query, so I cannot change the
> SQL it uses. I only control the values in the column.)
>
|||JD
select * from #FooBar order by case when foo ='z' then 1 else 2 end
"JD" <yazoo@.newsgroup.nospam> wrote in message
news:%23uiqZB3nIHA.3556@.TK2MSFTNGP04.phx.gbl...
> I've searched and have not found any good answers to this. Maybe there
> isn't one...
> I need to have a string that when sorted with an ORDER BY, it comes after
> the letter 'z'.
> The database is created with the collation SQL_Latin1_General_CP1_CI_AS.
> I know it is best to not rely on this and use some sort of surrogate field
> to order the rows, but I cannot do that in this case - the data is being
> used by SharePoint (MOSS) in a Business Data Catalog (BDC) column.
> SharePoint will sort one of it's lists by the BDC column value, but it
> sorts only by the values in that column.
> In ASCII, I could use the tilde (~), but SQL is putting the tilde first in
> the results.
> Is there any character I could put at the beginning of the string so it
> would fall out last in the query results?
> (Remember, it is SharePoint generating the query, so I cannot change the
> SQL it uses. I only control the values in the column.)
>
|||As I said, I cannot change the query, since Sharepoint is doing the query.
I can only affect the data in the column.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uVGG%2305nIHA.4328@.TK2MSFTNGP03.phx.gbl...
> JD
> select * from #FooBar order by case when foo ='z' then 1 else 2 end
>
> "JD" <yazoo@.newsgroup.nospam> wrote in message
> news:%23uiqZB3nIHA.3556@.TK2MSFTNGP04.phx.gbl...
>
|||Thanks - it is nvarchar, so I made a few changes to your example and found a
series of Unicode characters that sort after 'z':
set nocount on
create table #FooBar(Foo nvarchar(10))
insert #FooBar (Foo) values ('A')
declare @.i int
set @.i = 900
while (@.i < 1200)
begin
insert #FooBar (Foo) values (nchar(@.i))
set @.i = @.i + 1
end
insert #FooBar (Foo) values ('z')
select Foo, unicode(Foo)
from #FooBar
where Foo > 'z'
order by Foo
drop table #FooBar
"Tom Cooper" <tomcooper@.comcast.no.spam.please.net> wrote in message
news:O6sC0c3nIHA.4832@.TK2MSFTNGP06.phx.gbl...
>I assume the column is char or varchar? If so, you can use the value
>CHAR(208), for example,
> Set NoCount ON
> Create Table #FooBar(Foo varchar(10))
> Insert #FooBar (Foo) Values ('A')
> Insert #FooBar (Foo) Values (Char(208))
> Insert #FooBar (Foo) Values ('z')
> Select Foo, Ascii(Foo)
> From #FooBar
> Order By Foo
> go
> Drop Table #FooBar
> You can find all the characters which will sort after 'z' with the
> following code
> Set NoCount ON
> Create Table #FooBar(Foo varchar(10))
> Insert #FooBar (Foo) Values ('A')
> Insert #FooBar (Foo) Values ('z')
> Declare @.i int
> Set @.i = 1
> While @.i < 256
> Begin
> Insert #FooBar (Foo) Values (Char(@.i))
> Set @.i = @.i + 1
> End
> Select Foo, Ascii(Foo)
> From #FooBar
> Where Foo > 'z'
> Order By Foo
> go
> Drop Table #FooBar
> Tom
> "JD" <yazoo@.newsgroup.nospam> wrote in message
> news:%23uiqZB3nIHA.3556@.TK2MSFTNGP04.phx.gbl...
>

Character greater than 'z'

I've searched and have not found any good answers to this. Maybe there
isn't one...
I need to have a string that when sorted with an ORDER BY, it comes after
the letter 'z'.
The database is created with the collation SQL_Latin1_General_CP1_CI_AS.
I know it is best to not rely on this and use some sort of surrogate field
to order the rows, but I cannot do that in this case - the data is being
used by SharePoint (MOSS) in a Business Data Catalog (BDC) column.
SharePoint will sort one of it's lists by the BDC column value, but it sorts
only by the values in that column.
In ASCII, I could use the tilde (~), but SQL is putting the tilde first in
the results.
Is there any character I could put at the beginning of the string so it
would fall out last in the query results?
(Remember, it is SharePoint generating the query, so I cannot change the SQL
it uses. I only control the values in the column.)I assume the column is char or varchar? If so, you can use the value
CHAR(208), for example,
Set NoCount ON
Create Table #FooBar(Foo varchar(10))
Insert #FooBar (Foo) Values ('A')
Insert #FooBar (Foo) Values (Char(208))
Insert #FooBar (Foo) Values ('z')
Select Foo, Ascii(Foo)
From #FooBar
Order By Foo
go
Drop Table #FooBar
You can find all the characters which will sort after 'z' with the following
code
Set NoCount ON
Create Table #FooBar(Foo varchar(10))
Insert #FooBar (Foo) Values ('A')
Insert #FooBar (Foo) Values ('z')
Declare @.i int
Set @.i = 1
While @.i < 256
Begin
Insert #FooBar (Foo) Values (Char(@.i))
Set @.i = @.i + 1
End
Select Foo, Ascii(Foo)
From #FooBar
Where Foo > 'z'
Order By Foo
go
Drop Table #FooBar
Tom
"JD" <yazoo@.newsgroup.nospam> wrote in message
news:%23uiqZB3nIHA.3556@.TK2MSFTNGP04.phx.gbl...
> I've searched and have not found any good answers to this. Maybe there
> isn't one...
> I need to have a string that when sorted with an ORDER BY, it comes after
> the letter 'z'.
> The database is created with the collation SQL_Latin1_General_CP1_CI_AS.
> I know it is best to not rely on this and use some sort of surrogate field
> to order the rows, but I cannot do that in this case - the data is being
> used by SharePoint (MOSS) in a Business Data Catalog (BDC) column.
> SharePoint will sort one of it's lists by the BDC column value, but it
> sorts only by the values in that column.
> In ASCII, I could use the tilde (~), but SQL is putting the tilde first in
> the results.
> Is there any character I could put at the beginning of the string so it
> would fall out last in the query results?
> (Remember, it is SharePoint generating the query, so I cannot change the
> SQL it uses. I only control the values in the column.)
>|||JD
select * from #FooBar order by case when foo ='z' then 1 else 2 end
"JD" <yazoo@.newsgroup.nospam> wrote in message
news:%23uiqZB3nIHA.3556@.TK2MSFTNGP04.phx.gbl...
> I've searched and have not found any good answers to this. Maybe there
> isn't one...
> I need to have a string that when sorted with an ORDER BY, it comes after
> the letter 'z'.
> The database is created with the collation SQL_Latin1_General_CP1_CI_AS.
> I know it is best to not rely on this and use some sort of surrogate field
> to order the rows, but I cannot do that in this case - the data is being
> used by SharePoint (MOSS) in a Business Data Catalog (BDC) column.
> SharePoint will sort one of it's lists by the BDC column value, but it
> sorts only by the values in that column.
> In ASCII, I could use the tilde (~), but SQL is putting the tilde first in
> the results.
> Is there any character I could put at the beginning of the string so it
> would fall out last in the query results?
> (Remember, it is SharePoint generating the query, so I cannot change the
> SQL it uses. I only control the values in the column.)
>|||As I said, I cannot change the query, since Sharepoint is doing the query.
I can only affect the data in the column.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uVGG%2305nIHA.4328@.TK2MSFTNGP03.phx.gbl...
> JD
> select * from #FooBar order by case when foo ='z' then 1 else 2 end
>
> "JD" <yazoo@.newsgroup.nospam> wrote in message
> news:%23uiqZB3nIHA.3556@.TK2MSFTNGP04.phx.gbl...
>> I've searched and have not found any good answers to this. Maybe there
>> isn't one...
>> I need to have a string that when sorted with an ORDER BY, it comes after
>> the letter 'z'.
>> The database is created with the collation SQL_Latin1_General_CP1_CI_AS.
>> I know it is best to not rely on this and use some sort of surrogate
>> field to order the rows, but I cannot do that in this case - the data is
>> being used by SharePoint (MOSS) in a Business Data Catalog (BDC) column.
>> SharePoint will sort one of it's lists by the BDC column value, but it
>> sorts only by the values in that column.
>> In ASCII, I could use the tilde (~), but SQL is putting the tilde first
>> in the results.
>> Is there any character I could put at the beginning of the string so it
>> would fall out last in the query results?
>> (Remember, it is SharePoint generating the query, so I cannot change the
>> SQL it uses. I only control the values in the column.)
>>
>|||Thanks - it is nvarchar, so I made a few changes to your example and found a
series of Unicode characters that sort after 'z':
set nocount on
create table #FooBar(Foo nvarchar(10))
insert #FooBar (Foo) values ('A')
declare @.i int
set @.i = 900
while (@.i < 1200)
begin
insert #FooBar (Foo) values (nchar(@.i))
set @.i = @.i + 1
end
insert #FooBar (Foo) values ('z')
select Foo, unicode(Foo)
from #FooBar
where Foo > 'z'
order by Foo
drop table #FooBar
"Tom Cooper" <tomcooper@.comcast.no.spam.please.net> wrote in message
news:O6sC0c3nIHA.4832@.TK2MSFTNGP06.phx.gbl...
>I assume the column is char or varchar? If so, you can use the value
>CHAR(208), for example,
> Set NoCount ON
> Create Table #FooBar(Foo varchar(10))
> Insert #FooBar (Foo) Values ('A')
> Insert #FooBar (Foo) Values (Char(208))
> Insert #FooBar (Foo) Values ('z')
> Select Foo, Ascii(Foo)
> From #FooBar
> Order By Foo
> go
> Drop Table #FooBar
> You can find all the characters which will sort after 'z' with the
> following code
> Set NoCount ON
> Create Table #FooBar(Foo varchar(10))
> Insert #FooBar (Foo) Values ('A')
> Insert #FooBar (Foo) Values ('z')
> Declare @.i int
> Set @.i = 1
> While @.i < 256
> Begin
> Insert #FooBar (Foo) Values (Char(@.i))
> Set @.i = @.i + 1
> End
> Select Foo, Ascii(Foo)
> From #FooBar
> Where Foo > 'z'
> Order By Foo
> go
> Drop Table #FooBar
> Tom
> "JD" <yazoo@.newsgroup.nospam> wrote in message
> news:%23uiqZB3nIHA.3556@.TK2MSFTNGP04.phx.gbl...
>> I've searched and have not found any good answers to this. Maybe there
>> isn't one...
>> I need to have a string that when sorted with an ORDER BY, it comes after
>> the letter 'z'.
>> The database is created with the collation SQL_Latin1_General_CP1_CI_AS.
>> I know it is best to not rely on this and use some sort of surrogate
>> field to order the rows, but I cannot do that in this case - the data is
>> being used by SharePoint (MOSS) in a Business Data Catalog (BDC) column.
>> SharePoint will sort one of it's lists by the BDC column value, but it
>> sorts only by the values in that column.
>> In ASCII, I could use the tilde (~), but SQL is putting the tilde first
>> in the results.
>> Is there any character I could put at the beginning of the string so it
>> would fall out last in the query results?
>> (Remember, it is SharePoint generating the query, so I cannot change the
>> SQL it uses. I only control the values in the column.)
>>
>