complex Sorting - 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

SQL Server 2005 Forums

Transact-SQL (2005)

complex Sorting

Author

Topic

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2008-03-29 : 03:45:51

hi,
i need to sort a column using order by
this is o/p of query

id Values
----- -----------
235516 US $ 50.00
235517 US $ 50.00
235521 US $ 10.00
184094 50.00%
199004 50.00%
198985 100.00%
198986 100.00%
214946 US $ 32333.00
220313 US $ 32333.00
220309 11.00%
235524 TransWorld Motocross Subscription

from the above result u can see two columns (id,value) . i need to sort value column.
as
US $ 10.00
US $ 50.00
50.00 %
100.00 %
TransWorld Motocross Subscription

i need result like this when i sort out values column. is it possible.pls help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-29 : 10:45:27

Try like this:-

SELECT id,Values,
       CASE WHEN CHARINDEX('$',Values) >0 THEN 1
            WHEN CHARINDEX('%',Values) >0 THEN 2
            ELSE 3
       END AS OrderCategory,
       CASE WHEN CHARINDEX('$',Values) >0 THEN SUBSTRING(Values,CHARINDEX('$',Values)+2,LEN(Values)-CHARINDEX('$',Values) -1)
           WHEN CHARINDEX('%',Values) >0 THEN LEFT(Values,CHARINDEX('%',Values)-1)
           ELSE Values
           END AS OrderValue
FROM Table
ORDER BY OrderCategory,OrderValue

nr
SQLTeam MVY

12543 Posts

Posted - 2008-03-29 : 14:28:58

select value
from tbl
order by
case when value like 'US $ %' then 1 when value like '% ^%' escape '^' then 2 else 3 end ,
right(space(40) + replace(value, 'US $ ', ''), 40)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2008-03-31 : 02:27:12

Thanks both of u for ur help and both the query was helpful.

Thanks once again and vishak query was effective and i implemented it and it works fine. Accurate!. thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources