Get Negative Values and positive values in order

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 2000 Forums

Transact-SQL (2000)

Get Negative Values and positive values in order

Author

Topic

gbnaveen
Starting Member

10 Posts

Posted - 2008-08-18 : 08:00:25

How to get the negative & positive values in order, below is the scenario

Actual Results when values retrieved with order by clause.
-99.4%
99.5%
-99.6%
-99.7%
99.8%
99.9%

Expecting results should be as below,

-99.4%
-99.6%
-99.7%
99.5%
99.8%
99.9%

are there any ways to get the records as above

Thanks,
Naveen

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-18 : 08:04:53

What's the datatype of field containing the values?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-18 : 08:09:09

i'm getting it correctly now

SELECT *
FROM
(
SELECT '-99.4%' AS Val UNION ALL
SELECT '-99.6%' UNION ALL
SELECT '-99.7%' UNION ALL
SELECT '99.5%'	UNION ALL
SELECT '99.8%' UNION ALL
SELECT '99.9%'

)t
ORDER BY Val

output
---------------------
Val
------
-99.4%
-99.6%
-99.7%
99.5%
99.8%
99.9%

gbnaveen
Starting Member

10 Posts

Posted - 2008-08-18 : 08:19:44

the values are not stored in table as i am building them dynamically by "order by (abs((amount2-amount1)/amount1) * 100)
ASC"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-18 : 08:21:48

dont take abs. just use expression without abs

gbnaveen
Starting Member

10 Posts

Posted - 2008-08-18 : 08:26:57

hey visakh16, thanks for u r quick reply.
i'll try without abs and i have one more doubt,
is the order of negative values should be as -99.4%,-99.6%,-99.7% order or shuld be in reverse like -99.7,-99.6,-99.4?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-18 : 08:33:18

quote:
Originally posted by gbnaveen

hey visakh16, thanks for u r quick reply.
i'll try without abs and i have one more doubt,
is the order of negative values should be as -99.4%,-99.6%,-99.7% order or shuld be in reverse like -99.7,-99.6,-99.4?

should be reverse -99.7,-99.6,-99.4

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-18 : 08:36:25

[code]SELECT *
FROM
(
SELECT '-99.4%' AS Val UNION ALL
SELECT '-99.6%' UNION ALL
SELECT '-99.7%' UNION ALL
SELECT '99.5%' UNION ALL
SELECT '99.8%' UNION ALL
SELECT '99.9%'

)t
ORDER BY
CAST(REPLACE(Val,'%','') AS real)

output
-----
Val
------
-99.7%
-99.6%
-99.4%
99.5%
99.8%
99.9%[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-18 : 08:38:42

quote:

Originally posted by visakh16

i'm getting it correctly now

SELECT *
FROM
(
SELECT '-99.4%' AS Val UNION ALL
SELECT '-99.6%' UNION ALL
SELECT '-99.7%' UNION ALL
SELECT '99.5%'	UNION ALL
SELECT '99.8%' UNION ALL
SELECT '99.9%'

)t
ORDER BY Val

output
---------------------
Val
------
-99.4%
-99.6%
-99.7%
99.5%
99.8%
99.9%

If you sort them by varchar, order wont come correctly

SELECT *
FROM
(
SELECT '-299.4%' AS Val UNION ALL
SELECT '-199.7%' UNION ALL
SELECT '99.5%' UNION ALL
SELECT '-99.6%' UNION ALL
SELECT '99.8%' UNION ALL
SELECT '99.9%'

)t
ORDER BY Val

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-18 : 11:51:24

quote:
Originally posted by madhivanan

quote:
Originally posted by visakh16

i'm getting it correctly now

SELECT *
FROM
(
SELECT '-99.4%' AS Val UNION ALL
SELECT '-99.6%' UNION ALL
SELECT '-99.7%' UNION ALL
SELECT '99.5%' UNION ALL
SELECT '99.8%' UNION ALL
SELECT '99.9%'

)t
ORDER BY Val

output
---------------------
Val
------
-99.4%
-99.6%
-99.7%
99.5%
99.8%
99.9%

If you sort them by varchar, order wont come correctly

SELECT *
FROM
(
SELECT '-299.4%' AS Val UNION ALL
SELECT '-199.7%' UNION ALL
SELECT '99.5%' UNION ALL
SELECT '-99.6%' UNION ALL
SELECT '99.8%' UNION ALL
SELECT '99.9%'

)t
ORDER BY Val

Madhivanan

Failing to plan is Planning to fail

yup i know that

i was just showing OP his given output can be obtained even by sorting with varchar.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources