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
