SQL statement

[KeithH]

Anyone out there with SQL Knowledge?

I have the following statement on my Basestation.sqb file . . .

SELECT ModeS, Registration, Manufacturer, Type, ICAOTypeCode, SerialNo, RegisteredOwners, UserTag, OperatorFlagCode, UserNotes, Interested
FROM Aircraft
where Registration like "G%"
-- and RegisteredOwners like "Air China"
-- and UserTag <> ""
order by RegisteredOwners, Registration

(the first line is too long for the input field on the forum).  But it doesn't seem to want to sort correctly!

I have run this in both SQLite Pro and SQLight Expert with the same result.  All seems good until close to the end of my list when the sort appears to start again.  So for example EasyJet appears at the end of the listing, after say Thomas Cook.  This is a general purpose statement that I have inserted comments "--" to stop lines 4 & 5 executing this time.  I have also tried the statement without these lines.

For the life of me I cannot see where my error is. 

I started a new Basestation.sqb file yesterday and thought I'd see how it was populating and noticed this seemingly error in the SQLite programs.

Can anyone spot my error (if one exists)?

Thanks

[IanH]

Just tried your SQL on my Basestation.sqb using SQLite Expert.

Worked as expected with easyJet appearing ahead of Thomson and registrations for each sorted correctly.

Make sure that you have the latest version of SQLite Expert - previous problems with SQL code from someone else were a result of an old version not behaving as expected.

I'm using version 3.5.85.205 of sqlite expert personal.

[KeithH]

Many thanks,

Taking another look this morning I've just realised what is happening!  I have EasyJet spelt "easyJet" (lower case "E").  It is that that SQLite is sorting after upper case first characters.  It's not a show stopper but I'll have a look around the internet to see if I can find any code that will sort e and E together.

My database is populated using PlaneBase which spells EasyJet the correct way causing this.

Many thanks for trying on your system.

Keith

[KeithH]

Many thanks,

Taking another look this morning I've just realised what is happening!  I have EasyJet spelt "easyJet" (lower case "E").  It is that that SQLite is sorting after upper case first characters.  It's not a show stopper but I'll have a look around the internet to see if I can find any code that will sort e and E together.

My database is populated using PlaneBase which spells EasyJet the correct way causing this.

Many thanks for trying on your system.

Keith

And fixed.

I should be using the UPPER command statement.  So my final SQL looks like this. . .

SELECT ModeS, Registration, Manufacturer, Type, ICAOTypeCode, SerialNo, RegisteredOwners, UserTag, OperatorFlagCode, UserNotes, Interested
FROM Aircraft
where Registration like "G%"
-- and RegisteredOwners like "Air China"
-- and UserTag <> ""
order by UPPER(RegisteredOwners), Registration

Simples!

Thanks again.

Keith