SitePoint Sponsor

User Tag List

Results 1 to 12 of 12

Hybrid View

  1. #1
    SitePoint Enthusiast
    Join Date
    Sep 2004
    Location
    new york
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL ORDER BY Problem

    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.

  2. #2
    SitePoint Member
    Join Date
    May 2004
    Location
    belgium
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    sort order

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

  3. #3
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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)

  4. #4
    SitePoint Enthusiast
    Join Date
    Sep 2004
    Location
    new york
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 ?

  5. #5
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  6. #6
    SitePoint Enthusiast
    Join Date
    Sep 2004
    Location
    new york
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    Code:
    <%
    
    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.

  7. #7
    SEOChat has too many ads Protoss's Avatar
    Join Date
    Aug 2004
    Location
    Long Island, NY
    Posts
    248
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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"

  8. #8
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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".

  9. #9
    SitePoint Enthusiast
    Join Date
    Sep 2004
    Location
    new york
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  10. #10
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Perhaps you should change the definition of the field so that it accepts NULL values.

  11. #11
    SEOChat has too many ads Protoss's Avatar
    Join Date
    Aug 2004
    Location
    Long Island, NY
    Posts
    248
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you are using Enterprise Manager in SQL, simply go to the field and hit CTRL+0 to enter NULL.

  12. #12
    SitePoint Enthusiast
    Join Date
    Sep 2004
    Location
    new york
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •