Listbox sorting after SQLite Query

  1. 5 months ago

    brian f

    Jun 11 Pre-Release Testers, Xojo Pro Chilly California

    I have a SQLite query that fills a listbox with 5 columns in ASC order from that query

    -image-

    Please excuse some of the dates as I just populated a listbox for display purposes

    What I want to do is sort the listbox based on column 0, the "Operation Number" column.
    The first 2 digits are for the year and the last 3 after the "-" are for the job number. Of course if I sort Ascending it will start at "00-XXX" down to "99-XXX" or if Descending it would sort "99-XXX" to "00-XXX"

    What I would like is to have the listbox show the current year "19" first then "18" , "17" etc.

    I'm not using CompareRows as I'm not allowing the user to click the header for custom sorting

    Thought of using an array of years and trying to sort from that but figured I would ask those smarter then me what they might try

    I thought about using the SQL Date/Time but that is not always guaranteed to be there plus some jobs are closed and some are open

    right by year desc and job # asc
    which is, unfortunately going to be work since your operations #'s are conjoined data (year-job #)

    you might need to look at sqlite string functions to split that operation # into its two pieces : year and job # so you can sort by each piece

    personally operation number would, if I designed the db, NOT be stored like this and would be "derived" from 2 columns, year and job # which would make this trivial since you could just do and order by on year DESC and job # ASC

    try something like (assuming the operation # column is called OPERATION_NUMBER)

    ORDER BY substr(OPERATION_NUMBER,1, instr(OPERATION_NUMBER,"-")-1)  DESC, substr(OPERATION_NUMBER, instr(OPERATION_NUMBER,"-")+1)  ASC
  2. Norman P

    Jun 11 Pre-Release Testers, Xojo Pro Outside

    why not just sort DESC in your initial query ?
    or do you want sort desc by year then asc by job ?
    that you can do as well by customizing the order by clause on your initial query to break out the year and job # and sorting by those

  3. brian f

    Jun 11 Pre-Release Testers, Xojo Pro Chilly California

    @Norman P

    To describe it best the resulting "Operation Number" column would look like this:

    Operation Number
    ____________________
    
    19-001
    19-002
    19-003
    19-004
    19-XXX
    18-799
    18-798
    18-797
    18-796
    18-XXX
    17-XXX

    Hopefully I explained it good enough

  4. Emile S

    Jun 11 Europe (France, Strasbourg)

    Custom Sort ?

    http://docs.xojo.com/ListBox.SortColumn

  5. Norman P

    Jun 11 Pre-Release Testers, Xojo Pro Answer Outside
    Edited 5 months ago

    right by year desc and job # asc
    which is, unfortunately going to be work since your operations #'s are conjoined data (year-job #)

    you might need to look at sqlite string functions to split that operation # into its two pieces : year and job # so you can sort by each piece

    personally operation number would, if I designed the db, NOT be stored like this and would be "derived" from 2 columns, year and job # which would make this trivial since you could just do and order by on year DESC and job # ASC

    try something like (assuming the operation # column is called OPERATION_NUMBER)

    ORDER BY substr(OPERATION_NUMBER,1, instr(OPERATION_NUMBER,"-")-1)  DESC, substr(OPERATION_NUMBER, instr(OPERATION_NUMBER,"-")+1)  ASC
  6. brian f

    Jun 11 Pre-Release Testers, Xojo Pro Chilly California

    Hi Emile

    I would but I'm not allowing the user to click the header for sorting

  7. brian f

    Jun 11 Pre-Release Testers, Xojo Pro Chilly California

    Norm,

    I agree and would love to have the "Operation Number" derived from 2 columns but unfortunately I'm just working with what pass passed down to me for now until I can refactor and talk my boss into allowing me more time to fix this project correctly.

  8. Norman P

    Jun 11 Pre-Release Testers, Xojo Pro Outside

    well you _could_ split the data right now with something like the end of my last post which _should_ split it into the left part before the - and the right part after the -

  9. brian f

    Jun 11 Pre-Release Testers, Xojo Pro Chilly California

    Norman,

    The ORDER BY gives me the results I want so thank you. I ran them in DB Browser for SQLite tool and the Query executed correctly, but for some reason if I add the ORDER BY clause you provided I get this error from Xojo:

    -image-

    This is the Query String:

    sql = "SELECT OP_NUMBER, OPEN_CLOSE, LOCATION, SETUP, COMPLETION FROM OP ORDER BY (substr(OP_NUMBER,1, instr(OP_NUMBER,"-") -1) DESC, substr(OP_NUMBER, instr(OP_NUMBER,"-" +1) DESC"
  10. Alberto D

    Jun 11 Pre-Release Testers

    Don't you need "" somewhere?

  11. Norman P

    Jun 11 Pre-Release Testers, Xojo Pro Outside

    missing quotes in your string

    sql = "SELECT OP_NUMBER, OPEN_CLOSE, LOCATION, SETUP, COMPLETION FROM OP ORDER BY (substr(OP_NUMBER,1, instr(OP_NUMBER,""-"") -1) DESC, substr(OP_NUMBER, instr(OP_NUMBER,""-"" +1) DESC"
  12. brian f

    Jun 11 Pre-Release Testers, Xojo Pro Chilly California

    DOH! as Homer would say

    LOL to funny as I could have stared at that error all day :)

    Thanks Everyone

  13. Dave S

    Jun 11 San Diego, California USA

    FYI.... double double quotes are not necessary.... single quotes can be used inside an SQL Query

  14. Norman P

    Jun 11 Pre-Release Testers, Xojo Pro Outside

    I just didnt write the code that way so .........

  15. brian f

    Jun 11 Pre-Release Testers, Xojo Pro Chilly California

    Just throwing this in just in case someone else finds if useful

    I also added this to the query to weed out job numbers from the 90's (I have another search query that allows for viewing records before this millennium)

    substr(OP_NUMBER, 1, 1)!='9'

    So full query would be:

    "SELECT OP_NUMBER, OPEN_CLOSE, LOCATION, SETUP, COMPLETION FROM OP WHERE substr(OP_NUMBER, 1, 1)!='9' ORDER BY substr(OP_NUMBER,1, instr(OP_NUMBER,""-"")-1) DESC, substr(OP_NUMBER, instr(OP_NUMBER,""-"")+1) DESC"

or Sign Up to reply!