I have a SQLite query that fills a listbox with 5 columns in ASC order from that query
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
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
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
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.
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 -
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:
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"
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"
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"