SQL Sort help

Databases
1

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.

aaa_001.yyy
aaa_001.zzz
aaa_002.yyy
aaa_002.zzz
aaa_003.yyy
aaa_003.zzz
bbb_001.yyy
bbb_002.yyy
bbb_003.yyy

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:

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

What I really want is the following:

aaa_001.yyy
aaa_002.yyy
aaa_003.yyy
aaa_001.zzz
aaa_002.zzz
aaa_003.zzz
bbb_001.yyy
bbb_002.yyy
bbb_003.yyy

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.

2

SELECT * FROM table ORDER BY name, ext

3

Order by substr(name, 1, 3), ext, substr(name, 5,3) should do it for you.

4

I tried SELECT * FROM Table Order by substr(name, 1, 3), ext, substr(name, 5,3)

still not the right results it returned

aaa_.yyy
bbb_.yyy
aaa_*.zzz

I’m looking for

aaa_.yyy
aaa_.zzz
bbb_*.yyy

5

Order by substr(name, 1, 3), ext, substr(name, 8,3) should do it for you.

6

sorry. still not working

7

This

[code] Dim db As New SQLiteDatabase

Call db.Connect
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
rst.MoveNext
Loop
[/code]

results in a list box with the rows in the order you wanted.

8

strange your example works, but it doesn’t work with the data i actually have in the database.

9

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.

10

What database are you using?

11

This is what happens when the examples aren’t actually the real data… :slight_smile:

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.
aaa*
bbb*

etc

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)