whaw... I could never come up with solutions like this. Will this work with SQLite? Where can I read more about this?
Op zo 24 jun. 2018 om 20:29 schreef R Smith <rsm...@rsweb.co.za>: > > > On 2018/06/22 11:05 PM, Dan Kennedy wrote: > > On 06/23/2018 03:52 AM, R Smith wrote: > >> > >> 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. > > > > What would the window-function query be? > > Mainly one can avoid the self-join with a windowing function, like this: > > SELECT F2, F1 FROM (SELECT ROW_COUNT() OVER (PARTITION BY F2 ORDER BY > F1) AS C, F2, F1) AS XWHERE X.C <= 10 > > or > > SELECT F2, F1 > FROM ( > SELECT F2, F1, DENSE_RANK() OVER (PARTITION BY F2 ORDER BY F1 > DESC) AS R > ) AS X > WHERE R <= 10 > > > > _______________________________________________ > 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
