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
