Barry, that's even easier indeed. And it works perfect! Thanks for sharing this.
gert Op za 23 jun. 2018 om 14:32 schreef Barry Smith <smith.bar...@gmail.com>: > Ryan's way works well. Here is a second method which expresses it in a > different way: > > SELECT * FROM T t1 where rowid IN (SELECT rowid FROM T t2 WHERE t1.F2 = > t2.F2 ORDER BY rowid LIMIT 10) > > If you have WITHOUT ROWID tables you'd have to replace rowid with your > primary key. > > (The query may still work without the ORDER BY, I didn't test it, but even > if it does a future query optimiser might break that because without the > order by the results of the inner select are free to include a different 10 > rowids for every value in the outer query) > > > On 23 Jun 2018, at 9:50 pm, Gert Van Assche <ger...@gmail.com> wrote: > > > > Hi Ryan, thanks for this. This is working if the F1 field is a numeric > > value. With text (especially Asian & Arabic characters) this does not > seem > > to work. > > So I created an MD5 hash from the text fields and it works great! Thank > you > > so much. > > > > gert > > > > Op vr 22 jun. 2018 om 22:52 schreef R Smith <ryansmit...@gmail.com>: > > > >> > >>> On 2018/06/22 10:04 PM, Gert Van Assche wrote: > >>> All, > >>> > >>> I'm sure it must be possible, I just don't find how. > >>> I have a table T with 2 fields (F1 and F2). The F1 are unique and the > F2 > >>> are not unique. > >>> I would like to get only 10 F1 fields for each unique F2. > >> > >> This is not normally done, and windowing functions in other RDBMSes > >> makes for an easier way, but it can still be done in SQLite with some > >> creative grouping of a self-joined query. > >> In this example, I limited it to 3 F1 items per unique F2 for brevity, > >> but you can easily change the "< 4" to "< 11" or "<= 10" according to > >> preference. > >> > >> -- SQLite version 3.20.1 [ Release: 2017-08-24 ] on SQLitespeed > >> version 2.0.2.4. > >> -- > >> > >> > ================================================================================================ > >> > >> CREATE TABLE T(F1 INTEGER PRIMARY KEY, F2 TEXT); > >> > >> INSERT INTO T(F1,F2) VALUES > >> (1, 'John') > >> ,(2, 'John') > >> ,(3, 'Jason') > >> ,(4, 'John') > >> ,(5, 'Jason') > >> ,(6, 'John') > >> ,(7, 'John') > >> ,(8, 'Jason') > >> ,(9, 'Jason') > >> ,(10, 'Joan') > >> ,(11, 'Joan') > >> ,(12, 'Joan') > >> ,(13, 'Jimmy') > >> ; > >> > >> SELECT A.F2, B.F1 > >> FROM T AS A > >> JOIN T AS B ON B.F2 = A.F2 AND B.F1 >= A.F1 > >> GROUP BY A.F2, B.F1 > >> HAVING COUNT(*) < 4 > >> ; > >> > >> -- F2 | F1 > >> -- ----- | --- > >> -- Jason | 3 > >> -- Jason | 5 > >> -- Jason | 8 > >> -- Jimmy | 13 > >> -- Joan | 10 > >> -- Joan | 11 > >> -- Joan | 12 > >> -- John | 1 > >> -- John | 2 > >> -- John | 4 > >> > >> > >> > >> -- Another option to note, in case the 10 limit is not important and > >> simply aimed > >> -- at saving space, is to use group concatenation, like so: > >> > >> SELECT F2, group_concat(F1)AS F1 > >> FROM T > >> GROUP BY F2 > >> ; > >> > >> -- | > >> -- F2 | F1 > >> -- ----- | --------- > >> -- Jason | 3,5,8,9 > >> -- Jimmy | 13 > >> -- Joan | 10,11,12 > >> -- John | 1,2,4,6,7 > >> > >> > >> -- > >> > >> > ------------------------------------------------------------------------------------------------ > >> > >> Cheers! > >> Ryan > >> > >> > >> _______________________________________________ > >> sqlite-users mailing list > >> sqlite-users@mailinglists.sqlite.org > >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > >> > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
