Re: [sqlite] Query question
I'm no expert, but I'd do it with: SELECT COUNT(*) AS count FROM ratingsTable GROUP BY mtId ORDER BY count DESC LIMIT 1; :-David Burström On 01/25/2011 03:49 PM, Puneet Kishor wrote: > > Ian Hardingham wrote: >> Hey guys. >> >> I have the following table: >> >> ratingsTable (id INTEGER PRIMARY KEY AUTOINCREMENT, mtId INTEGER, user >> TEXT, rating INTEGER); >> >> mtId links to another table's primary key >> >> I wish to have a query which gives me the mtId which is represented most >> often in the ratingsTable. >> >> Does anyone have any advice? > > a combination of Max() and Count() > > Puneet. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] JOIN bug in 3.7.2, not in 3.6.22
Dan, Richard, Igor, thanks for your input, and yes, it seems as if the gamble is no longer safe. Hopefully I'm the only one that has run into this side effect ;) :-David On 01/17/2011 04:57 PM, Igor Tandetnik wrote: > David Burström wrote: >> SELECT starttime, endtime from entry LEFT JOIN interval ON >> interval.entryid = entry.id GROUP BY entry.id HAVING starttime = >> MAX(starttime); > The behavior of this statement is unspecified. In standard SQL, it is > syntactically invalid - in a GROUP BY statement, all columns that appear in > SELECT or HAVING clauses must be either mentioned in GROUP BY, or appear only > in expressions that are arguments of aggregate functions. > > As an extension, SQLite allows ungrouped columns in these contexts. The value > of such an expression is the column value taken from some random, unspecified > row belonging to the group. > > Thus, your condition of "HAVING starttime = MAX(starttime)" is a gamble. You > are saying, pick some random row from the group, and if that row just happens > to contain the largest value across the group, then include the group in the > resultset, otherwise throw it away. With 3.6.22, you got lucky and won the > gamble. With 3.7.2, the implementation details have changed so that a > different row is now being picked, and you are losing the gamble (and blaming > the roulette wheel). ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] JOIN bug in 3.7.2, not in 3.6.22
I just tried the 3.7.4 binary on Linux, and the bug is still around. :-David On 01/17/2011 04:25 PM, Black, Michael (IS) wrote: > This may be the patch that fixes your problem... > http://www.sqlite.org/src/info/ece641eb89 > > Was released in 3.7.3 > > Michael D. Black > Senior Scientist > Advanced Analytics Directorate > Northrop Grumman Information Systems > > > > > From: sqlite-users-boun...@sqlite.org on behalf of David Burström > Sent: Mon 1/17/2011 6:47 AM > To: sqlite-users@sqlite.org > Subject: EXTERNAL:[sqlite] JOIN bug in 3.7.2, not in 3.6.22 > > > > Hello all! > > I stumbled across this strange bug during Android development on 2.2.1 > late last night. Please run the following snippet in SQLite 3.7.2 and > 3.6.22 to compare the differences. The comments shows what alterations > you can make to make the query return the expected result. > > > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] JOIN bug in 3.7.2, not in 3.6.22
Hello all! I stumbled across this strange bug during Android development on 2.2.1 late last night. Please run the following snippet in SQLite 3.7.2 and 3.6.22 to compare the differences. The comments shows what alterations you can make to make the query return the expected result. :-David Burström .header on .mode column .echo on -- -- The query below is supposed to return one row, starttime = 3 and endtime = 1. In SQLite 3.622 it works. In SQLite 3.7.2 it returns 0 rows. -- -- if endtime is in a different position in the table, the query works CREATE TABLE interval (endtime INTEGER, entryid INTEGER, starttime INTEGER); CREATE TABLE entry (id INTEGER PRIMARY KEY AUTOINCREMENT); INSERT INTO entry (id) VALUES ( 42); INSERT INTO interval (endtime, entryid, starttime) VALUES (2, 42, 1); -- if the endtime below is greater than or equal to the endtime above, the query works INSERT INTO interval (endtime, entryid, starttime) VALUES (1, 42, 3); -- if endtime is removed from the projection, the query works -- if the LEFT JOIN is changed to INNER JOIN, the query works SELECT starttime, endtime from entry LEFT JOIN interval ON interval.entryid = entry.id GROUP BY entry.id HAVING starttime = MAX(starttime); ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
