adding All option to a sorted list

Hi.
My DB holds a table with a varchar column containg numbers.
In RS I need to create a dataset that will sort these numbers and add 'All'
value to this dataset.
My problems is:
As the dataset holds sting values and it performs lexicographic sort, if I
perform cast to int on these numbers (to sort them correctly) I can't add the
'All' value.
Furthermore, Does anyone knows how to refer to a custom made dataset defined
in the RS?
Thanks,
GuyTry something like
select right('0000' + myvarcharfield,4) from table order by 1
union
select 'All'
for a set of numbers not longer than 4 characters... This will sort them
correctly I think without converting them to ints
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"GuyR" <GuyR@.discussions.microsoft.com> wrote in message
news:56FE3261-1DEF-48DD-B4B3-07F2F992AC4A@.microsoft.com...
> Hi.
> My DB holds a table with a varchar column containg numbers.
> In RS I need to create a dataset that will sort these numbers and add
> 'All'
> value to this dataset.
> My problems is:
> As the dataset holds sting values and it performs lexicographic sort, if I
> perform cast to int on these numbers (to sort them correctly) I can't add
> the
> 'All' value.
> Furthermore, Does anyone knows how to refer to a custom made dataset
> defined
> in the RS?
> Thanks,
> Guy
>