SitePoint Sponsor

User Tag List

Results 1 to 21 of 21
  1. #1
    SitePoint Evangelist
    Join Date
    Jul 2005
    Posts
    456
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    ordering numbers?

    hi,
    i need to oredr a set of numbers. the numbers are "06/9","95/1","95/6","06/3" and so on. the thing is that these need to be ordered form the second number, so it shoudl be ordered thus: "95/1","06/3","95/6","06/9".
    how can i do this?
    at the moment just as an attempt im splitting the number either side of the "/" like this:
    var regNo = "12/09"
    var yearStart = 0
    var yearEnd = regNo.indexOf("/")
    var year = regNo.substring(yearStart, yearEnd)
    var itemStart = regNo.indexOf("/") + 1
    var itemEnd = regNo.length
    var itemNo = regNo.substring(itemStart, itemEnd)
    but where do i go form here?
    thanks.

  2. #2
    ********* user
    Join Date
    Mar 2006
    Location
    Malta
    Posts
    312
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The different sorting algorithms

    http://atschool.eduweb.co.uk/mbaker/sorts.html

  3. #3
    SitePoint Evangelist
    Join Date
    Jul 2005
    Posts
    456
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi,
    i have a field called regNo in my database with values being fed out of it - "06/9","95/1","95/6","06/3" and so on. i need these sorted as mentioned above. could i do this within the sql? or maybe have another field in my db whose default value is whatever is entered into the regNo field but split up in a way that i can order that field? does that make sense?

  4. #4
    SitePoint Member
    Join Date
    Oct 2006
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can do it in the SQL yes, something like...

    ORDER BY CSng(Right([YOURFIELD],Len([YOURFIELD])-InStr([YOURFIELD],'/')))

  5. #5
    SitePoint Evangelist
    Join Date
    Jul 2005
    Posts
    456
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    that sounds like what i need! ill have a look for some tutorials on the subject.

  6. #6
    SitePoint Evangelist
    Join Date
    Jul 2005
    Posts
    456
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi ihitman,
    ive been looking at string functions, but dont know how to do this. the field that contains my number is called: RegisteredNo. ive tried using your code thusly:
    ORDER BY CSng(Right('RegisteredNo',Len('RegisteredNo')-InStr('RegisteredNo','/')))

    but its saying 'data type mismatch'. i have the field set as Text within access. maybe ive implemented your code wrongly?
    many thanks

  7. #7
    SitePoint Member
    Join Date
    Oct 2006
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yep, you have. You've replaced the square brackets with single quote marks for some reason.

    ORDER BY CSng(Right([RegisteredNo],Len([RegisteredNo])-InStr([RegisteredNo],'/')))

    ...try that instead.

  8. #8
    SitePoint Evangelist
    Join Date
    Jul 2005
    Posts
    456
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi ihitman, this is all my sql:

    SELECT tbCategory.*, tbItems.*, tbItems.designation, tbItems.disposal, tbItems.undertaking FROM tbCategory INNER JOIN tbItems ON tbCategory.catID = tbItems.catID WHERE tbItems.designation=true AND tbItems.disposal=false AND tbItems.undertaking=false ORDER BY CSng(Right([RegisteredNo],Len([RegisteredNo])-InStr([RegisteredNo],'/')))

    but its still coming up with Data type mismatch in criteria expression? RegisteredNo is a text field in my db.
    what could that be to do with? if i just have an "order by RegisteredNo" i get:

    00/1
    00/10
    00/11
    00/12
    02/14
    02/15
    02/16
    02/17
    02/18
    02/19
    02/2
    02/3... and so on!

    any ideas?
    thanks for your help on this.

  9. #9
    SitePoint Member
    Join Date
    Oct 2006
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try this...

    ORDER BY CSng(Right(CStr([RegisteredNo]),Len(CStr([RegisteredNo]))-InStr(CStr([RegisteredNo]),'/')))

    ...what database are you using here by the way?

  10. #10
    SitePoint Evangelist
    Join Date
    Jul 2005
    Posts
    456
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hhhhmmmm.... im still getting a data type mismatch?

    im using access for my database and asp (jscript). the RegisteredNo field is stored as a basic text field.

  11. #11
    SitePoint Member
    Join Date
    Oct 2006
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm assuming the RegisteredNo appears in only one of the tables? If not, try putting [theCorrectTable].[RegisteredNo] in the SQL as well for each time the field is referenced.

    Asides from that, don't have a clue why it's bringing up a type mismatch error.

  12. #12
    SitePoint Wizard
    Join Date
    Nov 2004
    Location
    Nelson BC
    Posts
    2,310
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I made a table in access with 1 field RegisteredNo (text), I copied and pasted the values you posted and then ran this query:
    Code:
    SELECT RegisteredNo FROM Table1
    ORDER BY CSng(Right(CStr([RegisteredNo]),Len(CStr([RegisteredNo]))-InStr(CStr([RegisteredNo]),'/')))
    And it worked just fine.

    Maybe it's some other field that's causing the mismatch.

  13. #13
    SitePoint Evangelist
    Join Date
    Jul 2005
    Posts
    456
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi jim, that works great! do you know what was happening? after manually going through the thousands of records, one was marked as NAN and one was blank! i guess that explains the mismatch!
    i was speaking to the client yesterday and hes only just enlightened me on the relevance of this number. basically the '##/##' format is 'year/regNo'. so what hes actually asking is to have it ordered by year, then within that ordering the regNo!!! so if we have 06/01, 05/02, 06/02/, 04/12, then it should be oredred: 06/02, 06/01, 05/02, 04/12 so that the latest registered item within the latest year is shown first. doh!!!!!!! the site is due for launch by next week and he only decides to tell me this now!
    is this stupidly difficult to do? youve been a massive help already and i dont want to take the p**s. if you could suggest a way i could do this and ill figure it out. many thanks.

  14. #14
    SitePoint Member
    Join Date
    Oct 2006
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Nah, tis no problem, just a point of ordering by the first half of the value, using a similar bit of SQL as the first bit, change your ORDER BY statement to this and it will do it.

    ORDER BY CSng(Left(CStr([RegisteredNo]),InStr(CStr([RegisteredNo]),'/')-1)) DESC , CSng(Right(CStr([RegisteredNo]),Len(CStr([RegisteredNo]))-InStr(CStr([RegisteredNo]),'/'))) DESC

    ...again though, as you've discovered, this code will only work if the data is formatted in a way that the SQL is expecting here, if not, it'll knack.

  15. #15
    SitePoint Wizard
    Join Date
    Nov 2004
    Location
    Nelson BC
    Posts
    2,310
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    <smack target="yourclient" location="inthehead" reason="for concatenating 2 perfectly good fields into one" />

  16. #16
    SitePoint Wizard
    Join Date
    Nov 2004
    Location
    Nelson BC
    Posts
    2,310
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here is a mega error-checking super-query that is guaranteed to fail in any real database setting and is hugely inefficient but seems to get the job done in access - obviously the fields I selected are for testing but run it on your table (replacing table1 with the real name) and see if it works for ya
    Code:
    SELECT RegisteredNo, 
    Iif(InStr(RegisteredNo,'/') > 0, Iif(IsNumeric(Right(RegisteredNo, Len(RegisteredNo) - InStr(RegisteredNo, '/'))),CSng(Right(RegisteredNo, Len(RegisteredNo) - InStr(RegisteredNo, '/'))) , -1), -1), 
    Iif(InStr(RegisteredNo,'/') > 0, Iif(IsNumeric(Left(RegisteredNo, InStr(RegisteredNo, '/') - 1)),CSng(Left(RegisteredNo, InStr(RegisteredNo, '/') - 1)) , -1), Iif(IsNumeric(RegisteredNo),CSng(RegisteredNo),-1))
     FROM Table1
    ORDER BY
        Iif(InStr(RegisteredNo,'/') > 0, Iif(IsNumeric(Right(RegisteredNo, Len(RegisteredNo) - InStr(RegisteredNo, '/'))),CSng(Right(RegisteredNo, Len(RegisteredNo) - InStr(RegisteredNo, '/'))) , -1), -1) ASC,
        Iif(InStr(RegisteredNo,'/') > 0, Iif(IsNumeric(Left(RegisteredNo, InStr(RegisteredNo, '/') - 1)),CSng(Left(RegisteredNo, InStr(RegisteredNo, '/') - 1)) , -1), Iif(IsNumeric(RegisteredNo),CSng(RegisteredNo),-1)) ASC
    I tested it with values like:
    00/1
    20/20
    /11
    <nothing>
    /
    1/

    If the value of part1 is blank or invalid, it uses -1 as the sort value
    same as part 2
    if there is no slash it assumes the value of the field goes in part1

    That's a lot of work and annoying inefficiency just because 2 fields were combined into 1

  17. #17
    SitePoint Wizard
    Join Date
    Nov 2004
    Location
    Nelson BC
    Posts
    2,310
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Having said that, what I would do is create a query object in access that contains all the fields of the original table PLUS the "split" registeredNo fields...

    Let's say that RegisteredNo is a field in tbItems (you never specified).
    I'd use Access to create a query named vtbItems.
    That query would be the following:
    Code:
    SELECT 
        *, 
        IIf(InStr(RegisteredNo,'/')>0,IIf(IsNumeric(Right(RegisteredNo,Len(RegisteredNo)-InStr(RegisteredNo,'/'))),CSng(Right(RegisteredNo,Len(RegisteredNo)-InStr(RegisteredNo,'/'))),-1),-1) AS V_RegNo,
        IIf(InStr(RegisteredNo,'/')>0,IIf(IsNumeric(Left(RegisteredNo,InStr(RegisteredNo,'/')-1)),CSng(Left(RegisteredNo,InStr(RegisteredNo,'/')-1)),-1),IIf(IsNumeric(RegisteredNo),CSng(RegisteredNo),-1)) AS V_Year
    FROM tbItems;
    Then, in your ASP code, refer to the query object instead of the table in all your queries - you can then sort by the extra fields V_RegNo and V_Year:
    Code:
    SELECT 
    	tbCategory.*, 
    	vtbItems.*
    FROM tbCategory INNER JOIN vtbItems ON tbCategory.catID = vtbItems.catID 
    WHERE tbItems.designation=true AND tbItems.disposal=false AND tbItems.undertaking=false 
    ORDER BY vtbItems.V_RegNo ASC, vtbItems.V_Year ASC
    This way, if I had to run this query on different ASP pages, I wouldn't have to use that god-awful order by clause ever again

  18. #18
    SitePoint Evangelist
    Join Date
    Jul 2005
    Posts
    456
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    oh my god guys, i cant believe the work youve both put in to help me! apologies for the late reply, i had a well earned day off yesterday!
    anyway....
    i think ive got a problem here. your codes work fine (ive learnt a lot!), but as you see here: http://www.websitevision.co.uk/test/
    its going to be impossible to order these numbers because of their fomat. eg: the code quite rightly orders the numbers in descending order, but it orders the years: 99/23, 99/22..... before 06/19,06/18,06/17 (because obviosuly 99 is higher than 06, even though we know that 2006 comes after 1999!).
    do you think theres a way around this? if not, then some monkey (probably me!) will have to redo all the codes and put the years in their full format (2006 instead of 06), OR reinput all the data in the database in the correct order!! chr*st, what a palava!

  19. #19
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,631
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    What is the oldest year in the database?

    Logically Jim's Access Query object can be amended to pad the V_Year value with 19 or 20 ... if less than 10 then make it 200X else make it 19XX. That way the order by on V_Year will result correctly.

    UNTESTED!!

    Code:
    SELECT 
        *, 
        IIf(InStr(RegisteredNo,'/')>0,IIf(IsNumeric(Right(RegisteredNo,Len(RegisteredNo)-InStr(RegisteredNo,'/'))),CSng(Right(RegisteredNo,Len(RegisteredNo)-InStr(RegisteredNo,'/'))),-1),-1) AS V_RegNo,
        IIf(InStr(RegisteredNo,'/')>0,IIf(IsNumeric(Left(RegisteredNo,InStr(RegisteredNo,'/')-1)),IIf(Left(RegisteredNo,InStr(RegisteredNo,'/')-1)<10,CONCAT('200',Left(RegisteredNo,InStr(RegisteredNo,'/')-1)),CONCAT('19',Left(RegisteredNo,InStr(RegisteredNo,'/')-1))),CSng(Left(RegisteredNo,InStr(RegisteredNo,'/')-1)),-1),IIf(IsNumeric(RegisteredNo),CSng(RegisteredNo),-1)) AS V_Year
    FROM tbItems;
    Ian Anderson
    www.siteguru.co.uk

  20. #20
    SitePoint Evangelist
    Join Date
    Jul 2005
    Posts
    456
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi siteguru, im getting:

    Microsoft JET Database Engine (0x80040E14)
    Wrong number of arguments used with function in query expression 'IIf(InStr(RegisteredNo,'/')>0,IIf(IsNumeric(Left(RegisteredNo,InStr(RegisteredNo,'/')-1)),IIf(Left(RegisteredNo,InStr(RegisteredNo,'/')-1)<10,CONCAT('200',Left(RegisteredNo,InStr(RegisteredNo,'/')-1)),CONCAT('19',Left(RegisteredNo,InStr(RegisteredNo,'/')-'.


    the oldest year is gonna be about 1970 (not earlier than 1900).
    apologies for brief message, about to go into a meeting.
    - G

  21. #21
    Learning...
    Join Date
    Jan 2003
    Posts
    781
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you got some excellent help above so i have nothing to add to that part.

    just...
    while ordering you need to keep in mind that strings will behave differently from integers. If your numbers are like 1,2,21,3,22,12,10 the result would be

    Integer
    1,2,3,10,12,21,22

    String
    1,10,12,2,21,22,3

    you have to type cast your strings into integers to get the right ordering.
    The beauty of life is not dependent on how happy you are,
    but on how happy others can be because of you...


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
  •