Re: [sqlite] Help with a self join please
... and it runs in under half the time of my version, including showing the percentage and selecting only those over 75% -- View this message in context: http://sqlite.1065341.n5.nabble.com/Help-with-a-self-join-please-tp71242p71281.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help with a self join please
Thanks Igor, that looks neater than my solution. -- View this message in context: http://sqlite.1065341.n5.nabble.com/Help-with-a-self-join-please-tp71242p71280.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help with a self join please
OK, solved it myself (always happens a day after I post a question!). The solution is SELECT ClassID, COUNT(StudentID ) AS Numstudents, SUM ( CASE WHEN (SELECT Points FROMGrades AS T WHERE T.ReportNumber = 6 AND T.classcode=Grades .classcode AND T.StudentID= Grades .StudentID ) - points > 0 THEN 1 ELSE 0 END) AS NumImproved, 100.0 * SUM ( CASE WHEN (SELECT Points FROMGrades AS T WHERE T.ReportNumber = 6 AND T.classcode=Grades .classcode AND T.StudentID= Grades .StudentID ) - points > 0 THEN 1 ELSE 0 END ) / COUNT(upn) AS PercentImproved FROM Grades WHEREReportNumber = 5 GROUP BY ClassID -- View this message in context: http://sqlite.1065341.n5.nabble.com/Help-with-a-self-join-please-tp71242p71255.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Dynamically generate SQL statements for SQLite
I agree with the other poster. You can just use any string manipluation routines you like to assemble the SQL into a string and then execute it. Take care with literals, eg where name = 'fred' might have to become where name = ''fred'' in Delphi (thats two single quotes at each end). If it's any help, two brilliant tools that I use are SQLinForm (http://www.sqlinform.com) and SQLite Expert (http://www.sqliteexpert.com/). SQLinForm lets me take some sql that I know works and with one click format it to generate a string in loads of languages. So for example the sql.. select name from people where age = 30; becomes SQL := '' +'SELECT name ' +'FROM people ' +'WHERE age = 30;'; and you can then copy/paste it into your editor and tweak it to accept variables at runtime instead. SqliteExpert is simply the best database management system for SQLite that I have come across (and I've tried a few!) I can try out my sql, make tables, see data and do all the manipulation I like. Two tools I wouldn't be without when developing. -- View this message in context: http://sqlite.1065341.n5.nabble.com/Dynamically-generate-SQL-statements-for-SQLite-tp71240p71243.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Help with a self join please
Please can somebody help me with this query. I have a table called Grades containing ClassID (text), StudentID (text), ReportNumber (integer), Points (Integer) ClassID, StudentID and ReportNumber can be used as a compound key to reference a particular Points value. I am trying to write a query, grouped by ClassID, to return the percentage of students in each class whose points for ReportNumber = 6 is more than the points for ReportNumber = 5 (for the same classID). The ReportNumbers can be hard coded for the moment. I might use parameters later. I'm looking for a result something like ClassIDPercentOfImprovedStudents = Eng1 56.6 Math2 60.3 Geog2 34.6 etc Ideally I'd also like the total number of students in each class and the number who have improved their points. (This is necessary to give meaning to the percentage.) I've tried playing around with self joins but am not getting very far. Any help would be gratefully received. Thanks -- View this message in context: http://sqlite.1065341.n5.nabble.com/Help-with-a-self-join-please-tp71242.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to select from a temp table with same name as a main table.
Thanks to everyone. I have always prefixed table names with the db name when I have attached dbs myself but I didn't appreciate that the temp database was 'attached' as such as well. In a nutshell then, if you only use one database then you can refer to tables with no prefix (although you can prefix with main if you like). But as soon as you either attach another db or use a temp database then it is wise to prefix all the tables with the database name to avoid any issues that might crop up with table names inadvertantly or deliberately being the same. Thanks, I've learnt something! -- View this message in context: http://sqlite.1065341.n5.nabble.com/How-to-select-from-a-temp-table-with-same-name-as-a-main-table-tp69470p69544.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to select from a temp table with same name as a main table.
That brilliant. It is as I thought. Odd behaviour though. I would have thought having no database prefix would default to the main, after all I don't put the prefix there usually and if you try to create two tables in the same db you get an error (obviously). To be on the safe side I've re-written the code to give the temp table a different name and altered all the sql to use the new name. That seems fine. I'll have to remember that issue though as it might crop up again if ever I inadvertantly give a temp table the same name as one alreay in main. regards Howard -- View this message in context: http://sqlite.1065341.n5.nabble.com/How-to-select-from-a-temp-table-with-same-name-as-a-main-table-tp69470p69475.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to select from a temp table with same name as a main table.
I have a table in the main database called 'students' and a two page block of sql that processes it, referring to it as simply 'students'. Experimenting with a different approach, I created a temp table, also called 'students' that contained a subset of the main.students. (I intended to modify the sql, replacing 'students' with 'temp.students' but have not yet done so) However, running my original sql now, before editing it, it appears to be using both the table 'students' and the table temp.students as it is now repeating some rows. Can sombody please explain what might be happening? If no database prefix is given, doesn't SQLite default to the 'main' database or does it somehow use a combination of all the tables with the same name regardless of database? -- View this message in context: http://sqlite.1065341.n5.nabble.com/How-to-select-from-a-temp-table-with-same-name-as-a-main-table-tp69470.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to restore from backup or alter db in multi-user envionment?
Simon, sensible point about being cautious and I'm sorry my original post caused a problem. However I can confirm to anyone reading this that DISQLite3 is a brilliant product. Easy to use, fast, very robust and one that allows you to communicate directly with SQLite3 databases without having to distribute any dlls. (That's what sold it to me.) It has never let me down and if I ever need help doing something unusual with it, Ralf is always quick to reply via the support mailing list. My query, for which I'm grateful for your reply Simon, was slightly more to do with SQLite3 generally which is why I posted it here and not in the DIOSqlite3 group. -- View this message in context: http://sqlite.1065341.n5.nabble.com/How-to-restore-from-backup-or-alter-db-in-multi-user-envionment-tp65812p65821.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to restore from backup or alter db in multi-user envionment?
Thank you Simon for a very full and informative reply. Howard Date: Sat, 1 Dec 2012 19:37:45 -0800 From: ml-node+s1065341n65817...@n5.nabble.com To: docshotma...@hotmail.com Subject: Re: How to restore from backup or alter db in multi-user envionment? On 1 Dec 2012, at 3:22pm, dochsm <[hidden email]> wrote: > My database is shared by many users on a network. As part of auto-updating my > client applications via the web it might be necessary to alter some table > structures / view sql or put extra data into tables inside the shared sqlite > database. Problem is that the db might be in use at the time. > > Qn1) Is it safe simply to wrap up everthing I want to do in a transaction > and then commit the transaction? As far as SQLite is concerned, yes. SQLite will lock the schema against changes while SELECT UPDATE INSERT commands are executing, and will lock the database against those commands while the database schema is being changed. The effect is that locking is used in the same way it would be if two users tried to do UPDATE commands at the same time. > Q2) If I back up the database first using the online backup to safely back > it up, how do I safely restore it again when the db might be in use at the > time? I assume you're talking about the online backup API: <http://www.sqlite.org/c3ref/backup_finish.html> Under those circumstances I think you would use the same backup API in the other direction to restore the database again. It will have a lock on that destination database the whole time it's working, of course. So if you have implemented a timeout correctly your users will just get a long pause or a "database busy" message during access. If you have been writing your code correctly you will have used transactions to group all related accesses /including related SELECTs/ together. So any particular piece of code in your app should notice only consistency. > I'm using DISQLite and the online api in delphi, not the command line thing. I have seen some bad drivers which assume that underlying schema will not be changed by another user while they have a database connection open. I have no reason to believe that this is one of them, but it might be worth reading the documentation. Simon. ___ sqlite-users mailing list [hidden email] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users If you reply to this email, your message will be added to the discussion below: http://sqlite.1065341.n5.nabble.com/How-to-restore-from-backup-or-alter-db-in-multi-user-envionment-tp65812p65817.html To unsubscribe from How to restore from backup or alter db in multi-user envionment?, click here. NAML -- View this message in context: http://sqlite.1065341.n5.nabble.com/How-to-restore-from-backup-or-alter-db-in-multi-user-envionment-tp65812p65818.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to restore from backup or alter db in multi-user envionment?
My database is shared by many users on a network. As part of auto-updating my client applications via the web it might be necessary to alter some table structures / view sql or put extra data into tables inside the shared sqlite database. Problem is that the db might be in use at the time. Qn1) Is it safe simply to wrap up everthing I want to do in a transaction and then commit the transaction? Q2) If I back up the database first using the online backup to safely back it up, how do I safely restore it again when the db might be in use at the time? I'm using DISQLite and the online api in delphi, not the command line thing. Howard -- View this message in context: http://sqlite.1065341.n5.nabble.com/How-to-restore-from-backup-or-alter-db-in-multi-user-envionment-tp65812.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
