Order By Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Order By Question

Author

Topic

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2009-06-03 : 08:01:29

Hi

I'm using SQL Server (v8 I think) and I have a view where some of the select values are made up of function calls with aliases.

One function in the view returns a string value with a number in it such as 'abcdef 123 ghi' and I want to order the results of the view by the number only. What currently happens is it orders like this if I order using the alias value:

abcdef 125 ghi
abcdef 126 ghi
abcdef 127 ghi
abcdef 126 ghi
abcdef 129 ghi
abcdef 13 ghi << Not ordering by full number
abcdef 130 ghi

Instead of:

abcdef 125 ghi
abcdef 126 ghi
abcdef 127 ghi
abcdef 126 ghi
abcdef 129 ghi
abcdef 130 ghi
abcdef 131 ghi

So I have created a function in preperation to try and order by the number returned from the new function but I am unsure how to combine it in to the view's query. The function is this:

CREATE FUNCTION dbo.NumbersFromString (@str varchar(100))  
RETURNS varchar(100) AS  
BEGIN 

WHILE PATINDEX('%[^0-9]%', @str )> 0
set @str = REPLACE(@str, SUBSTRING(@str, PATINDEX('%[^0-9]%', @str),1),'')
return @str

END

So if I run:

select dbo.NumbersFromString('abcdef 131 ghi')

I get:

131

But as I said I do not know how to try and use this function in the view's order by, if indeed I can or am I going about it the wrong way?

Thanks for any advice

G

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-06-03 : 10:23:53

[code]SELECT a.Something, a.SomethingElse
FROM (
SELECT Something, SomethingElse = dbo.YourOtherFunction(YourColumn)
FROM table
) AS a
ORDER BY dbo.NumbersFromString(a.SomethingElse)[/code]

- Lumbago

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-06-03 : 10:25:55

Or just

SELECT Something, SomethingElse
FROM yourView
ORDER BY dbo.NumbersFromString(SomethingElse)

I have to admit I was a little confused about your requirements...

- Lumbago

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2009-06-03 : 10:41:45

The requirement is to order the results of the view by one of selected columns but the column is a function with an alias. like this:

Select function(a, b) AS New, column1, column2
from whatever
order by New

Or is it

order by (select function(a, b)) etc

I do not know how to do the order by bit.

I need the select to return the original value i.e. 'abc 123 def' but order by only the numeric value i.e. order by '123' so user still sees the original value but ordered in the correct way.

Thanks

G

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-03 : 12:53:38

you can use like this:-


Select function(a, b) AS New, column1, column2
from whatever
order by New

make sure function is returning a numeric type else cast it to numeric and then use it in order by

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-06-03 : 13:05:44

You can also ORDER BY the selected column ordinal:

Select function(a, b) AS New, column1, column2
from whatever
order by 1

Be One with the Optimizer
TG

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2009-06-05 : 07:07:58

Yes but if I convert that to numeric will it not give an error because the returned value will be alphanumeric. I need to order by the number only in the field, as in:

abc 12
abc 13
abc 14
......
abc 109
abc 110

not

abc 119
abc 20

It seems to be ordering by the number in a way but starts at 100

then when it gets to 119 it goes

119
20

instead of 119 - 120

??

thanks

G

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2009-06-05 : 08:27:55

The procedure call in the view gets three values and I need to order the results from the view by one of the values in the procedure.

Thanks

G

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-06-05 : 09:35:34

if your function always returns a number then can you convert the value and set the return type to a numeric datatype? That way it should order properly.

or if the number is always an integer you can use the STR function to set the ording value: str(n, 15) will return like this:



----------
        12
        34
       119

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-05 : 13:04:11

quote:
Originally posted by Grifter

Yes but if I convert that to numeric will it not give an error because the returned value will be alphanumeric. I need to order by the number only in the field, as in:

abc 12
abc 13
abc 14
......
abc 109
abc 110

not

abc 119
abc 20

It seems to be ordering by the number in a way but starts at 100

then when it gets to 119 it goes

119
20

instead of 119 - 120

??

thanks

G

thats because return type of function is currently varchar. you need to change it to integer if you want it to be sorted based on numeric value

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2009-06-30 : 07:20:26

Hi

I have more or less got this function working bar one thing. I am ordering by a description first then this description size. Some of the products have only one number as the description size, others have a mix of one or two sizes in the description sizes.

In the second scenario I could have a size like this for say product A

A, 'Value 12, value 34'

And what happens is if there is another string with no second number like this in the second row of the order:

A, 'Value 5'

it will order like this:

A, 'Value 12, value 34'
A, 'Value 5'

Because the numbers stripped out and set like this:

1234
0005

But what I want to do is just order using the first number in the string before the comma so that it would order like this:

0005
0012

Then the order would look like:

A, 'Value 5'
A, 'Value 12, value 34'

Hence it would order by the first number instead of getting confused with second number.

Is there a way of doing this with the existing code shown below?

CREATE FUNCTION dbo.NumbersFromString (@str varchar(100))  --Value is originally a string
RETURNS varchar (10) AS  
BEGIN 

WHILE PATINDEX('%[^0-9]%', @str )> 0 -- Determines if there is any characters in the string, the number returned is where the first charcter in the string is identified
set @str = REPLACE(@str, SUBSTRING(@str, PATINDEX('%[^0-9]%', @str),1),'') -- Replace values other than numbers with nothing.
if LEN(@str) = 3
set @str = ('0' +@str) -- If number length is 3 append '0'
if LEN(@str) = 2 
set @str =  ('00' +@str) -- If number length is 2 append '00'
if LEN(@str) = 1
set @str = ('000' +@str) -- If number length is 1 append '000'
if @str = ''  
set @str = '0' -- If String length = 0 append '0'
return substring(@str, 1, 4)

END

this is the bit i'm stuck at and do not know if I could modify the existing code or need to start afresh.

Thanks for any helpful tips or comments on this

G

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-06-30 : 08:01:28

UDFs in views are not generally a good idea.
Try something like:

-- *** Test Data ***
DECLARE @test TABLE
(
    testString varchar(50) NOT NULL
)
INSERT INTO @test
SELECT 'abcdef 125 ghi' UNION ALL
SELECT 'abcdef 126 ghi' UNION ALL
SELECT 'abcdef 127 ghi' UNION ALL
SELECT 'abcdef 126 ghi' UNION ALL
SELECT 'abcdef 129 ghi' UNION ALL
SELECT 'abcdef 13 ghi' UNION ALL
SELECT 'abcdef 130 ghi' UNION ALL
SELECT 'No Number' UNION ALL
SELECT 'Value 12, value 34' UNION ALL
SELECT 'Value 5'
-- *** End Test Data ***

SELECT testString
FROM
(
    select * 
        ,SUBSTRING(testString, PATINDEX('%[0-9]%', testString), 255) As NString
    from @test
) D
ORDER BY
    CAST
    (
        CASE PATINDEX('%[^0-9]%', NString)
                WHEN 1
                THEN '0'
                WHEN 0
                THEN NString
                ELSE LEFT(NString, PATINDEX('%[^0-9]%', NString) - 1)
            END
        AS int
    )

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2009-06-30 : 09:25:59

Hi Ifor

Thanks I will have a look over that to try and understand it for myself.

G

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources