sorting problem

14 Sep 2006 8:19 AM

aidanc

I'm trying to write a query to sort a table by a column containing both
numerical and alphabetical values. The values are stored as strings.
The problem is that I need to sort numerical values numerically and
sort anything with alphabetical characters in it alphabetically.

For instance the column contains values such as :

1000
40g40
9
901
ddd5
lp101
lp202

By default the values are being sorted alphabetically as above. What i
am looking for is a kind of hybrid sorting as follows.

9              (treated as a long)
901          (treated as a long)
1000        (treated as a long)
40g40       (treated as a string)
ddd5        (treated as a string)
lp101        (treated as a string)
lp202         (treated as a string)

I tried the following:

SELECT * FROM table where isnumeric(mixedcolumn) ORDER BY
CLng(mixedColumn)union select * from table where NOT
isnumeric(mixedcolumn) ORDER BY mixedColumn

This won't work because its not possible to sort both select queries
independently and then 'union' them. I'm officially out of ideas.

Can anyone suggest a solution?

14 Sep 2006 8:27 AM

Erland Sommarskog

aidanc (fancygo***@gmail.com) writes:

> I tried the following:
>
> SELECT * FROM table where isnumeric(mixedcolumn) ORDER BY
> CLng(mixedColumn)union select * from table where NOT
> isnumeric(mixedcolumn) ORDER BY mixedColumn
>
> This won't work because its not possible to sort both select queries
> independently and then 'union' them. I'm officially out of ideas.

And it can't work, since to my knowing there is no CLng function in SQL
Server.

This may cut it:

   SELECT ... FROM tbl
   ORDER BY CASE WHEN col NOT LIKE '%[^0-9]%' THEN convert(int, col)
                 ELSE convert(int, 0x7FFFFFFF)
            END, col

If you are using another engine than SQL Server, I have no idea if this
will work or not. But since you asked in a newsgroup devoted to SQL Server,
so...

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

14 Sep 2006 9:10 AM

Uri Dimant

Hi

CREATE TABLE #Test
(
col VARCHAR(10)not null primary key
)
INSERT INTO #Test VALUES ('40g40')
INSERT INTO #Test VALUES ('A120')
INSERT INTO #Test VALUES ('1000')
INSERT INTO #Test VALUES ('lp102')
INSERT INTO #Test VALUES ('lp101')
INSERT INTO #Test VALUES ('9')

SELECT * FROM #test ORDER BY
substring(col, 0,patindex('%[0-9]%',col))+right ('00000' + substring(col,
patindex('%[0-9]%',col) , len(col)),5)

There is may be a way to create CLR function to do that job

Show quote

"aidanc" <fancygo***@gmail.com> wrote in message
news:1158221957.461768.170540@i3g2000cwc.googlegroups.com...
> I'm trying to write a query to sort a table by a column containing both
> numerical and alphabetical values. The values are stored as strings.
> The problem is that I need to sort numerical values numerically and
> sort anything with alphabetical characters in it alphabetically.
>
> For instance the column contains values such as :
>
> 1000
> 40g40
> 9
> 901
> ddd5
> lp101
> lp202
>
> By default the values are being sorted alphabetically as above. What i
> am looking for is a kind of hybrid sorting as follows.
>
> 9              (treated as a long)
> 901          (treated as a long)
> 1000        (treated as a long)
> 40g40       (treated as a string)
> ddd5        (treated as a string)
> lp101        (treated as a string)
> lp202         (treated as a string)
>
> I tried the following:
>
> SELECT * FROM table where isnumeric(mixedcolumn) ORDER BY
> CLng(mixedColumn)union select * from table where NOT
> isnumeric(mixedcolumn) ORDER BY mixedColumn
>
> This won't work because its not possible to sort both select queries
> independently and then 'union' them. I'm officially out of ideas.
>
> Can anyone suggest a solution?
>

14 Sep 2006 10:02 PM

Colin Dawson

Show quote

"aidanc" <fancygo***@gmail.com> wrote in message
news:1158221957.461768.170540@i3g2000cwc.googlegroups.com...
> I'm trying to write a query to sort a table by a column containing both
> numerical and alphabetical values. The values are stored as strings.
> The problem is that I need to sort numerical values numerically and
> sort anything with alphabetical characters in it alphabetically.
>
> For instance the column contains values such as :
>
> 1000
> 40g40
> 9
> 901
> ddd5
> lp101
> lp202
>
> By default the values are being sorted alphabetically as above. What i
> am looking for is a kind of hybrid sorting as follows.
>
> 9              (treated as a long)
> 901          (treated as a long)
> 1000        (treated as a long)
> 40g40       (treated as a string)
> ddd5        (treated as a string)
> lp101        (treated as a string)
> lp202         (treated as a string)
>
> I tried the following:
>
> SELECT * FROM table where isnumeric(mixedcolumn) ORDER BY
> CLng(mixedColumn)union select * from table where NOT
> isnumeric(mixedcolumn) ORDER BY mixedColumn
>
> This won't work because its not possible to sort both select queries
> independently and then 'union' them. I'm officially out of ideas.
>
> Can anyone suggest a solution?
>

Yep, It's actually quite easy.   You need to seperate the identify the
numbers, seperated them out, and sort them first. Everything that is left
must be a string so sort that second.   Here's my example....

Create Table #MyTable(
MyValue VarChar(10)
)

Insert Into #MyTable( MyValue ) Values( '1000' )
Insert Into #MyTable( MyValue ) Values( '40g40' )
Insert Into #MyTable( MyValue ) Values( '9' )
Insert Into #MyTable( MyValue ) Values( '901' )
Insert Into #MyTable( MyValue ) Values( 'ddd5' )
Insert Into #MyTable( MyValue ) Values( 'lp101' )
Insert Into #MyTable( MyValue ) Values( 'lp202' )
Go

Select
MyValue
From #MyTable
Order By
ISNUMERIC(MyValue) Desc,
Case ISNUMERIC(MyValue)
    When 1 Then Cast( MyValue as Float )
End Asc,
MyValue Asc

Drop Table #MyTable

The key is how the Order By is put together. There are three columns in
this order by, the first is a call to the ISNUMERIC function, it returns 0
for strings and 1 for numbers. As you wanted the numbers (for the example),
Sort this descending.

The second column is a case statement, the filters out the numbers, leaving
the strings out. It then casts the string to a float, and uses that to sort
the numbers - into number order.

The Third column is used to provide the string sorting part.

Regards

Colin Dawson
www.cjdawson.com

15 Sep 2006 12:58 AM

aidanc

Thanks for the help all.

I've tried all of the solutions offered but It's still not working.
I should have specified in my initial post that I'm using
Microsoft.Jet.OLEDB.4.0 with VB 6.
So I am possibly in the wrong group, but I couldn't find a more
suitable one.
Does anyone have any more ideas?

Colin Dawson wrote:

Show quote

> "aidanc" <fancygo***@gmail.com> wrote in message
> news:1158221957.461768.170540@i3g2000cwc.googlegroups.com...
> > I'm trying to write a query to sort a table by a column containing both
> > numerical and alphabetical values. The values are stored as strings.
> > The problem is that I need to sort numerical values numerically and
> > sort anything with alphabetical characters in it alphabetically.
> >
> > For instance the column contains values such as :
> >
> > 1000
> > 40g40
> > 9
> > 901
> > ddd5
> > lp101
> > lp202
> >
> > By default the values are being sorted alphabetically as above. What i
> > am looking for is a kind of hybrid sorting as follows.
> >
> > 9              (treated as a long)
> > 901          (treated as a long)
> > 1000        (treated as a long)
> > 40g40       (treated as a string)
> > ddd5        (treated as a string)
> > lp101        (treated as a string)
> > lp202         (treated as a string)
> >
> > I tried the following:
> >
> > SELECT * FROM table where isnumeric(mixedcolumn) ORDER BY
> > CLng(mixedColumn)union select * from table where NOT
> > isnumeric(mixedcolumn) ORDER BY mixedColumn
> >
> > This won't work because its not possible to sort both select queries
> > independently and then 'union' them. I'm officially out of ideas.
> >
> > Can anyone suggest a solution?
> >
>
> Yep, It's actually quite easy.   You need to seperate the identify the
> numbers, seperated them out, and sort them first. Everything that is left
> must be a string so sort that second.   Here's my example....
>
> Create Table #MyTable(
>   MyValue VarChar(10)
> )
>
> Insert Into #MyTable( MyValue ) Values( '1000' )
> Insert Into #MyTable( MyValue ) Values( '40g40' )
> Insert Into #MyTable( MyValue ) Values( '9' )
> Insert Into #MyTable( MyValue ) Values( '901' )
> Insert Into #MyTable( MyValue ) Values( 'ddd5' )
> Insert Into #MyTable( MyValue ) Values( 'lp101' )
> Insert Into #MyTable( MyValue ) Values( 'lp202' )
> Go
>
> Select
>   MyValue
> From #MyTable
> Order By
>   ISNUMERIC(MyValue) Desc,
>   Case ISNUMERIC(MyValue)
>     When 1 Then Cast( MyValue as Float )
>   End Asc,
>   MyValue Asc
>
> Drop Table #MyTable
>
>
> The key is how the Order By is put together. There are three columns in
> this order by, the first is a call to the ISNUMERIC function, it returns 0
> for strings and 1 for numbers. As you wanted the numbers (for the example),
> Sort this descending.
>
> The second column is a case statement, the filters out the numbers, leaving
> the strings out. It then casts the string to a float, and uses that to sort
> the numbers - into number order.
>
> The Third column is used to provide the string sorting part.
>
>
> Regards
>
> Colin Dawson
> www.cjdawson.com

15 Sep 2006 1:19 PM

Jim Underwood

Microsoft Jet? then you are using Access, not SQL Server? You should try
one of the Access groups.

The SQL below may work in Access if you use IF instead of CASE and whatever
is the access equivilant for isnumeric.

Show quote

"aidanc" <fancygo***@gmail.com> wrote in message
news:1158281922.429030.272700@h48g2000cwc.googlegroups.com...
> Thanks for the help all.
>
> I've tried all of the solutions offered but It's still not working.
> I should have specified in my initial post that I'm using
> Microsoft.Jet.OLEDB.4.0 with VB 6.
> So I am possibly in the wrong group, but I couldn't find a more
> suitable one.
> Does anyone have any more ideas?
>
>
> Colin Dawson wrote:
>
> > "aidanc" <fancygo***@gmail.com> wrote in message
> > news:1158221957.461768.170540@i3g2000cwc.googlegroups.com...
> > > I'm trying to write a query to sort a table by a column containing
both
> > > numerical and alphabetical values. The values are stored as strings.
> > > The problem is that I need to sort numerical values numerically and
> > > sort anything with alphabetical characters in it alphabetically.
> > >
> > > For instance the column contains values such as :
> > >
> > > 1000
> > > 40g40
> > > 9
> > > 901
> > > ddd5
> > > lp101
> > > lp202
> > >
> > > By default the values are being sorted alphabetically as above. What i
> > > am looking for is a kind of hybrid sorting as follows.
> > >
> > > 9              (treated as a long)
> > > 901          (treated as a long)
> > > 1000        (treated as a long)
> > > 40g40       (treated as a string)
> > > ddd5        (treated as a string)
> > > lp101        (treated as a string)
> > > lp202         (treated as a string)
> > >
> > > I tried the following:
> > >
> > > SELECT * FROM table where isnumeric(mixedcolumn) ORDER BY
> > > CLng(mixedColumn)union select * from table where NOT
> > > isnumeric(mixedcolumn) ORDER BY mixedColumn
> > >
> > > This won't work because its not possible to sort both select queries
> > > independently and then 'union' them. I'm officially out of ideas.
> > >
> > > Can anyone suggest a solution?
> > >
> >
> > Yep, It's actually quite easy.   You need to seperate the identify the
> > numbers, seperated them out, and sort them first. Everything that is
left
> > must be a string so sort that second.   Here's my example....
> >
> > Create Table #MyTable(
> >   MyValue VarChar(10)
> > )
> >
> > Insert Into #MyTable( MyValue ) Values( '1000' )
> > Insert Into #MyTable( MyValue ) Values( '40g40' )
> > Insert Into #MyTable( MyValue ) Values( '9' )
> > Insert Into #MyTable( MyValue ) Values( '901' )
> > Insert Into #MyTable( MyValue ) Values( 'ddd5' )
> > Insert Into #MyTable( MyValue ) Values( 'lp101' )
> > Insert Into #MyTable( MyValue ) Values( 'lp202' )
> > Go
> >
> > Select
> >   MyValue
> > From #MyTable
> > Order By
> >   ISNUMERIC(MyValue) Desc,
> >   Case ISNUMERIC(MyValue)
> >     When 1 Then Cast( MyValue as Float )
> >   End Asc,
> >   MyValue Asc
> >
> > Drop Table #MyTable
> >
> >
> > The key is how the Order By is put together. There are three columns in
> > this order by, the first is a call to the ISNUMERIC function, it returns
0
> > for strings and 1 for numbers. As you wanted the numbers (for the
example),
> > Sort this descending.
> >
> > The second column is a case statement, the filters out the numbers,
leaving
> > the strings out. It then casts the string to a float, and uses that to
sort
> > the numbers - into number order.
> >
> > The Third column is used to provide the string sorting part.
> >
> >
> > Regards
> >
> > Colin Dawson
> > www.cjdawson.com
>