Re: [sqlite] SQL top 3

Ah, yes, that works indeed nicely. Thanks for that. RBS On Fri, Nov 17, 2017 at 9:21 PM, David Raymond wrote: > I think this works even if there are less than 3 cities listed for a > country. If there's a tie for 3rd it'll show all of them. > > > create table countries (country text, city text,

Re: [sqlite] SQL top 3

I think this works even if there are less than 3 cities listed for a country. If there's a tie for 3rd it'll show all of them. create table countries (country text, city text, population int); insert into countries values ('UK', 'London', '10'), ('UK', 'Birmingham', 9), ('UK', 'Manchester', '8

[sqlite] SQL top 3

Say I have a table like this: CREATE TABLE COUNTRIES(COUNTRY TEXT, CITY TEXT, POPULATION INTEGER) What would be the SQL to get 3 cities for each country with the highest 3 populations for that country? So, for example for the United Kingdom it would show London, Birmingham, Manchester in that ord

Re: [sqlite] 1TB limit on encrypted database

To adjust the page size with the pragma command it has to be done either right at the start of a new database, before SQLite has created the file yet, or right before a vacuum command, and the vacuum command will then include the page size change as part of its process. Running the page_size pra

Re: [sqlite] 1TB limit on encrypted database

My code actually sets this to 2048, but it doesn't appear to stick (or get used). I am just setting using the PRAGMA command (same as I am now doing the max_page_count). Could this be an issue with an older copy of the Encryption package? I have not updated yet - am in the middle of other wor

Re: [sqlite] Starting with TCL

2017-11-17 13:51 GMT+01:00 Peter Da Silva : > Sqlite will perform the substitution of Tcl variables in a query. You can > flag the variable with a ‘$’ or with a ‘:’ (which makes it more like other > SQL APIs). > ​Yes, I found that. The disadvantage is that you have to have a variable with the cor

Re: [sqlite] Starting with TCL

2017-11-17 12:43 GMT+01:00 Cecil Westerhof : > I have the following: > set getLatestTeasStr { > SELECT Tea > FROM teaInStock > ORDER BY LastUsed DESC > LIMIT5 > ; > } > > But because I want to define the limit at runtime I want to change it

Re: [sqlite] Starting with TCL

On 11/17/17, Peter Da Silva wrote: > > $db eval { > SELECT Tea > FROM teaInStock > ORDER BY LastUsed DESC > LIMIT $nrToFetch; > } { > ... do something with $Tea ... > } > > This latter case works because the query is s

Re: [sqlite] Starting with TCL

Sqlite will perform the substitution of Tcl variables in a query. You can flag the variable with a ‘$’ or with a ‘:’ (which makes it more like other SQL APIs). So you can write: $db eval { SELECT Tea FROM teaInStock ORDER BY LastUsed DESC

Re: [sqlite] Starting with TCL

I have the following: set getLatestTeasStr { SELECT Tea FROM teaInStock ORDER BY LastUsed DESC LIMIT5 ; } But because I want to define the limit at runtime I want to change it to: set getLatestTeasStr { SELECT Tea FROM

Re: [sqlite] Starting with TCL

​In Bash I can use: continue 2 ​to continue not the current loop, but the loop surrounding it. This does not work in TCL. Is there another way to do this? -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://

Re: [sqlite] Starting with TCL

2017-11-17 9:57 GMT+01:00 Dan Kennedy : > > This gives: >> wrong # args: should be "for start test next command" >> while executing >> "for {t last_used loc} $teaChoices { >> puts $t >> } >> > > Sorry - [for] should be [foreach]. > > So with your query as above, you want: > > set teaCh

Re: [sqlite] Starting with TCL

On 16 Nov 2017, at 6:58pm, Richard Hipp wrote: > Everything in TCL is a function. On 17 Nov 2017, at 8:38am, Dan Kennedy wrote: > `In Tcl, array means associative array - a key-value structure like an STL > map. A list is a flat vector of values, like an STL vector or an array in > plain old

Re: [sqlite] Starting with TCL

Simon Slavin wrote: > I thought in Tcl everything was a function ? In Tcl, all values are strings. A list is a string with entries as words according to the Tcl syntax rules. A dictionary is a list with an even number of elements (key/value pairs). (An array is not a v

Re: [sqlite] Starting with TCL

​Nope, this one is: SELECT Tea FROM teaInStock ORDER BY "Last Used" DESC LIMIT5 ; ​ then $teaChoices contains three elements for each row returned by the query. The first of each set of three is the "tea", the second the "last used" value and the third the "

Re: [sqlite] Starting with TCL

On 17 Nov 2017, at 8:38am, Dan Kennedy wrote: > Not sure whether or not you really want an "array". In Tcl, array means > associative array - a key-value structure like an STL map. A list is a flat > vector of values, like an STL vector or an array in plain old C. I thought in Tcl everything

Re: [sqlite] Starting with TCL

2017-11-17 9:38 GMT+01:00 Dan Kennedy : > On 11/17/2017 03:20 PM, Cecil Westerhof wrote: > >> The folowing works: >> db eval ${getTeasToDrinkStr} { >> puts [format "%-30s %-10s %2s %d" ${Tea} ${Last Used} ${Location} >> ${Randomiser}] >> } >> ​ >> But I want to reuse what I get,

Re: [sqlite] Starting with TCL

On 11/17/2017 03:20 PM, Cecil Westerhof wrote: The folowing works: db eval ${getTeasToDrinkStr} { puts [format "%-30s %-10s %2s %d" ${Tea} ${Last Used} ${Location} ${Randomiser}] } ​ But I want to reuse what I get, so I tried the following: ​set teaChoices [db eval ${getTea

Re: [sqlite] Starting with TCL

The folowing works: db eval ${getTeasToDrinkStr} { puts [format "%-30s %-10s %2s %d" ${Tea} ${Last Used} ${Location} ${Randomiser}] } ​ But I want to reuse what I get, so I tried the following: ​set teaChoices [db eval ${getTeasToDrinkStr}] foreach tea [array names teaChoice