March 19, 2012 at 7:00 am
declare @t table (name nvarchar(50))
insert into @t
select 'count_1'
union select 'count_19'
union select 'count_2'
union select 'count_29'
union select 'count_3'
union select 'count_30'
union select 'count_100'
select * from @t c
WHERE c.name LIKE '%[0-9]%'
ORDER BY c.name asc
OUTPUT:
count_1
count_100
count_19
count_2
count_29
count_3
count_30
but expected output:
count_1
count_3
count_2
count_19
count_29
count_30
count_100
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 19, 2012 at 7:05 am
That is because you are ordering by characters. The only way you will be able to get your results in the order you want is to order by something else, or a substring cast as a numeric datatype.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 19, 2012 at 7:07 am
Something like this should get you there.
select * from @t c
WHERE c.name LIKE '%[0-9]%'
order by cast(replace(c.name, 'count_', '') as int)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 19, 2012 at 7:08 am
The output given is correct for the data type involved, you are sorting a string. Based solely on your sample data, one way to sort it is as follows:
declare @t table (name nvarchar(50))
insert into @t
select 'count_1'
union select 'count_19'
union select 'count_2'
union select 'count_29'
union select 'count_3'
union select 'count_30'
union select 'count_100'
select * from @t c
WHERE c.name LIKE '%[0-9]%'
ORDER BY cast(replace(c.name,'count_','') as int) asc
If your actually data is different, then this won't work. You will need to look carefully at your data to figure out how to sort it.
March 19, 2012 at 10:45 pm
Thanks Lynn but my actual requirement is declare @t table (name nvarchar(50))
insert into @t
select 'count_1'
union select 'acct_id'
union select 'count_19'
union select 'count_2'
union select 'count_29'
union select 'count_3'
union select 'count_30'
union select 'count_100'
union select 'count_stub'
union select 'prod_count_stub'
union select 'count_id'
union select 'count_sss'
select * from @t c
---WHERE c.name NOT LIKE '%[0-9]'
ORDER BY
CASE
WHEN c.name = 'acct_id' THEN 01
WHEN RIGHT(c.name,5) = '_stub' THEN 02
WHEN RIGHT(c.name,3) = '_id' THEN 04
WHEN c.name LIKE '%[0-9]' THEN cast(replace(c.name,'count_','') as int)
ELSE
5
END and i want all values having numeric at last but in sequence as you have created
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 19, 2012 at 11:56 pm
Not sure what you need exactly. Can you post the expected result based on your updated sample data above.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 20, 2012 at 12:00 am
Expected output
acct_id
count_stub
prod_count_stub
count_id
count_1
count_2
count_3
count_19
count_29
count_30
count_100
count_sss
means i need all values(having numbers as their suffix) in a sequence.
my order by clause is not working as expected :crying:
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 20, 2012 at 12:20 am
Hope this helps
declare @t table (name nvarchar(50))
insert into @t
select 'count_1'
union select 'acct_id'
union select 'count_19'
union select 'count_2'
union select 'count_29'
union select 'count_3'
union select 'count_30'
union select 'count_100'
union select 'count_stub'
union select 'prod_count_stub'
union select 'count_id'
union select 'count_sss'
select * from @t c
ORDER BY
CASE
WHEN c.name = 'acct_id' THEN 01
WHEN RIGHT(c.name,5) = '_stub' THEN 02
WHEN RIGHT(c.name,3) = '_id' THEN 03
WHEN c.name LIKE '%[0-9]' THEN 04
ELSE
5
END,
case when c.name LIKE '%[0-9]' then '' else c.name end, -- needed to sort "count_stub" and "prod_count_stub" properly
case when c.name LIKE '%[0-9]' then cast(replace(c.name,'count_','') as int) else 1 end -- needed to sort the numbers
Also check if your requirement could be fulfilled by some change through the front end.
I doubt if this is the optimal way to fulfill your requirement.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 20, 2012 at 12:42 am
@kinston,
i liked you reply it helped me but one doubt ?
do we really need
case when c.name LIKE '%[0-9]' then '' else c.name end -- needed to sort "count_stub" and "prod_count_stub" properly
this has been taken care by
WHEN RIGHT(c.name,5) = '_stub' THEN 02
.
Please explain
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 20, 2012 at 2:23 am
Bhuvnesh (3/20/2012)
@kinston,i liked you reply it helped me but one doubt ?
do we really need
case when c.name LIKE '%[0-9]' then '' else c.name end -- needed to sort "count_stub" and "prod_count_stub" properly
this has been taken care by
WHEN RIGHT(c.name,5) = '_stub' THEN 02
.
Please explain
If you want these 2 rows in alphabetical order, its needed.
If you don't use the 2nd ORDER BY, your result may be any one of the below
acct_id
count_stub
prod_count_stub
....
OR
acct_id
prod_count_stub
count_stub
....
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply