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
