I list of sequential files, some with the same name and different extension. some with different name and same prefix, The natural sort order is the following.
I have them loaded in the database as name and ext so I query SELECT * FROM table ORDER BY ext, name and I get the following:
What I really want is the following:
Is there a way to do it with sql or do I need to do another sort function with the query results.
Thanks in advance.
SELECT * FROM table ORDER BY name, ext
Order by substr(name, 1, 3), ext, substr(name, 5,3) should do it for you.
I tried SELECT * FROM Table Order by substr(name, 1, 3), ext, substr(name, 5,3)
still not the right results it returned
I’m looking for
Order by substr(name, 1, 3), ext, substr(name, 8,3) should do it for you.
[code] Dim db As New SQLiteDatabase
db.SQLExecute(“CREATE TABLE Files (name TEXT, ext TEXT);”)
db.SQLExecute("INSERT INTO Files (name, ext) VALUES (‘aaa_001’, ‘yyy’), (‘aaa_002’, ‘yyy’), " _
- "(‘aaa_003’, ‘yyy’), (‘bbb_001’, ‘yyy’), (‘bbb_002’, ‘yyy’), (‘bbb_003’, ‘yyy’), " _
- “(‘aaa_001’, ‘zzz’), (‘aaa_002’, ‘zzz’), (‘aaa_003’, ‘zzz’);”)
Dim rst As RecordSet = db.SQLSelect(“SELECT * FROM Files ORDER BY substr(name, 1, 3), ext, substr(name, 5,3);”)
Do Until rst.EOF
Listbox1.AddRow rst.Field(“name”).StringValue, rst.Field(“ext”).StringValue
results in a list box with the rows in the order you wanted.
strange your example works, but it doesn’t work with the data i actually have in the database.
i figured it out. i need to adjust the substr parameters to match the filename. the problem then is i would need to know the filename before i did the query.
What database are you using?
This is what happens when the examples aren’t actually the real data…
Can we at least rely on a single period in the name?
If so, we can work with that.
Order by substr(name,instr(name,'.'),3) , name;
the first part should return yyy, xxx, zzz etc
and sort by it
Then within that by the name.
If there is more than one period, then you could do the same by using the rightmost 3 characters (see subset with a negative parameter)