Retrieving values from a table in SQL

SQL tables
I have values stored in a table in SQL this order: -99.4%, 99.5%, -99.6%, -99.7%, 99.8%, 99.9% I'd like to retrieve them in this order: -99.4%, -99.6%, -99.7%, 99.5%, 99.8%, 99.9% The ORDER BY clause does not do so. Is there another clause I could use?

Answer Wiki

Thanks. We'll let you know when a new response is added.

The ORDER BY clause is the correct one to use for the sorting of data.

In what order is the data coming out?

If you are storing the % sign as part of the data, then you are storing the data in a text string, so SQL will sort it differently. Try converting the column to a numeric value and storing the number without the percent sign. SQL will then be able to sort your data correctly.

In oracle you could use the SIGN and ABS functions in your order-by clause to achieve what you want.
This example assumes the data is numeric, but if it is stored in a text string, string manipulations can be done to get the numeric part.

Let’s assume your field is called “PCT”.

select *
from TABLE_X
order by SIGN(pct),ABS(pct);

The SIGN function returns 1 for positive values, and -1 for negatives, and the ABS function returns the absolute value of a number.

If you are using other database than Oracle, I would expect some similar functions exist to do this.

Discuss This Question: 4  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • PeteJohnston
    The order that you have specified looks as if it is DESC if the number is negative but ASC if it is positive? I'm struggling to think of anything since just sorting on the absolute value (ABS?) won't give the sequence you asked for. If you do a UNION statement with the first part selecting only the negative values and the second part the positive would that allow you to have a different ORDER BY clause for each part or does ORDER BY have to apply to the full statment? I'm not sure. Any ideas?
    10 pointsBadges:
  • Denny Cherry
    Check out my SQL Server blog "SQL Server with Mr Denny" for more SQL Server information.
    69,130 pointsBadges:
  • carlosdl
    Pete, in a UNION only one ORDER-BY clause can be used (AFAIK), and it applies for the full statement. I added an idea to Mrdenny's answer, check it out.
    85,905 pointsBadges:
  • Thongtarget
    Great Info
    0 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: