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?
Dan.
-- 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
