Listbox sorting after SQLite Query

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

@Norman Palardy

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

Custom Sort ?

http://documentation.xojo.com/api/deprecated/listbox.html#listbox-sortColumn

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

Hi Emile

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

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.

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 -

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:

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"

Don’t you need “” somewhere?

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"

DOH! as Homer would say

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

Thanks Everyone

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

I just didnt write the code that way so …

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"