SQL ORDER BY Problem

[scottnyc]

I have the following data:



7500

9000

1000

10000

2000

3000



When I do an ORDER BY the data above is sorted as follows:



1000

10000

2000

3000

7500

9000



Does anyone know why this is happening?



I need it to sort as follows:



1000

2000

3000

7500

9000

10000





Any help would be much appreciated.

[Necrite]

it seems that your field is "text". You must use a "numeric" field (int4, integer, ...) and your data will be sorted in numerical order.

[asterix]

Hello,

which database are you using?

Your data is being character sorted.
Perhaps it is even stored as characters?
If it were stored as numbers, it would be sorted numerically, i.e. correctly.

Resolution:
a) you could change the column type where the data is to an integer data type
b) you could cast the data from character to inter and then sort on the cast:

select data from Table
order by cast(data as int)

[scottnyc]

thanks for the help. You were both right. I changed the data type and everything is working great now.

The only issue I have now is that I wanted to have the option of inputting text in addition to inputting numbers. This field is a price field. I changed the data type to currency and everything works good BUT I wanted the option to put in "NA"

ANy ideas ?

[asterix]

That is a bad idea.
It is not a good idea to store user-interface-related-data such as NA in a database where only currency data should be stored.

Each product has a price - a monetary, curency value. If you do not know what the price is then what you store is - not 0 - not NA - but:

NULL

Then when you come to formatting the price to display in the user interface (a web page perhaps?) you do the following logic (pseudocode):

if(database.price==NULL) then
Display ("NA"):
else
Display ("$ " + database.price):
end

[scottnyc]

Should I put NULL in the database fields where I want NA to be?

I am using an Access DB, VBscript on an ASP page, my code is:

<%

Set conn = server.createobject("adodb.connection")
DSNtemp="DRIVER={Microsoft Access Driver (*.mdb)}; "
DSNtemp=dsntemp & "DBQ=e:\inetpub\clients\fpdb\database.mdb"
conn.Open DSNtemp

SQLstmt = "select * from database ORDER BY vehicleprice "

Set rs = conn.Execute(SQLstmt)

  DO WHILE NOT rs.EOF
    Recid = rs("ID")
    vehicleprice = rs("vehicleprice")

    %>
    
    <font face="Arial" style="font-size: 9pt">$<%=vehicleprice%>.00</font><br>
    
    <%
    rs.MoveNext
  LOOP
  RS.Close
  Response.Write "</TABLE>"

conn.Close
Set conn = nothing
Set SQLstmt = nothing

%>

My results with my current data come out as:

$.00
$.00
$.00
$1000.00
$1500.00
$2000.00
$2200.00
$2500.00
$2800.00
$2945.00
$2950.00
$3500.00
$3500.00
$3500.00
$3500.00
$3500.00
$3995.00
$4500.00
$10900.00


It's now sorting properly but I need the first 3, which are empty in the database to show as NA on the webpage.

Thanks for your help.

[Protoss]

Just add some programming to check if the given value is zero. After the variable "vehicleprice" is set, put another line that says something like:

If vehicleprice=0 Then vehicleprice="NA"

[asterix]

Yes, and no.

The programming as you suggest will give him the answer he wants, yes.

But no, he has some data saying that the product costs $0.00.
Some products probably do cost $0.00 (information broshures and such). At least the possibility that some products mights actually be free should be kept in mind.

Whereas, if you *don't know* what the price is, the value stored should be NULL, not 0. You see the difference?

NULL = "I have no idea what the price is"
0 = "This item costs $ 0.00".

[scottnyc]

thanks for the help.

I tried entering NULL into the database in the vehicleprice field where I have nothing right now and the response I get that entering NULL is not valid as the field only allows numeric data.

I would also do the option If vehicleprice=0 Then vehicleprice="NA" BUT the problem with this is that I have $.00 automatically printing for each result.

[asterix]

Perhaps you should change the definition of the field so that it accepts NULL values.

[Protoss]

If you are using Enterprise Manager in SQL, simply go to the field and hit CTRL+0 to enter NULL.

[scottnyc]

I am using MS Access. How do I change the definition of the field to allow NULL?

Also should I put the If statement in the Do Loop or before it?

Thanks for the help.