Problem with RANK/DENSE_RANK in a subquery (dense_rank_grp undefined)
Hi all,
I have several tables which represent different sources (sensors) of data. I want to find out which sensors picked up the signal first, and thus I decided to join these tables together, rank on common criteria and then select the first ones. However, that fails with:
TypeException:user.s22_3[110]:'calc.dense_rank_grp' undefined in: calc.dense_rank_grp(X_505:date,X_507:str,X_509:int,X_511:int,X_513:lng); program contains errors
Any ideas how to fix this?
Cheers, Nikola
Data: CREATE TABLE taba ( aday DATE, key1 varchar(15), msid int, sid int, eid bigint, r bigint ); CREATE TABLE tabb ( aday DATE, key1 varchar(15), msid int, sid int, aid bigint, eid bigint, r bigint ); CREATE TABLE tabc ( aday DATE, key1 varchar(15), msid int, sid int, aid bigint, m bigint, r bigint );
COPY 3 RECORDS INTO taba FROM stdin USING DELIMITERS '|','\n'; 2016-05-02|inst1|675|10001|200|100 2016-05-02|inst1|675|10002|500|111 2016-05-02|inst1|789|10001|800|135
COPY 3 RECORDS INTO tabb FROM stdin USING DELIMITERS '|','\n'; 2016-05-02|inst1|675|10001|77|200|88 2016-05-02|inst1|675|10002|100|500|112 2016-05-02|inst1|789|10001|112|800|134
COPY 3 RECORDS INTO tabc FROM stdin USING DELIMITERS '|','\n'; 2016-05-02|inst1|675|10001|77|200|100 2016-05-02|inst1|675|10002|100|500|111 2016-05-02|inst1|789|10001|112|800|130
Query:
SELECT aday, key1, msid, sid, aid, eid, src, r, rank FROM ( SELECT * ,sys.DENSE_RANK() OVER (PARTITION BY aday, key1, msid, sid, eid ORDER BY r) as rank FROM ( (SELECT aday, key1, msid, sid, NULL as aid, eid, 'TABA' as src, r FROM taba) UNION (SELECT aday, key1, msid, sid, aid, eid, 'TABB' as src, r FROM tabb) UNION (SELECT aday, key1, msid, sid, aid, m as eid, 'TABC' AS src, r FROM tabc) ) AS dummy1 ) AS dummy2 WHERE rank = 1 ;
Results: sql>CREATE TABLE taba ( more>aday DATE, more>key1 varchar(15), more>msid int, more>sid int, more>eid bigint, more>r bigint more>); operation successful (4.102ms) sql>CREATE TABLE tabb ( more>aday DATE, more>key1 varchar(15), more>msid int, more>sid int, more>aid bigint, more>eid bigint, more>r bigint more>); operation successful (3.172ms) sql>CREATE TABLE tabc ( more>aday DATE, more>key1 varchar(15), more>msid int, more>sid int, more>aid bigint, more>m bigint, more>r bigint more>); operation successful (3.326ms) sql> sql>COPY 3 RECORDS INTO taba FROM stdin USING DELIMITERS '|','\n'; more>2016-05-02|inst1|675|10001|200|100 more>2016-05-02|inst1|675|10002|500|111 more>2016-05-02|inst1|789|10001|800|135 3 affected rows (88.249ms) sql> sql>COPY 3 RECORDS INTO tabb FROM stdin USING DELIMITERS '|','\n'; more>2016-05-02|inst1|675|10001|77|200|88 more>2016-05-02|inst1|675|10002|100|500|112 more>2016-05-02|inst1|789|10001|112|800|134 3 affected rows (59.917ms) sql> sql>COPY 3 RECORDS INTO tabc FROM stdin USING DELIMITERS '|','\n'; more>2016-05-02|inst1|675|10001|77|200|100 more>2016-05-02|inst1|675|10002|100|500|111 more>2016-05-02|inst1|789|10001|112|800|130 3 affected rows (89.804ms) sql> sql>select * from taba; +------------+-------+------+-------+------+------+ | aday | key1 | msid | sid | eid | r | +============+=======+======+=======+======+======+ | 2016-05-02 | inst1 | 675 | 10001 | 200 | 100 | | 2016-05-02 | inst1 | 675 | 10002 | 500 | 111 | | 2016-05-02 | inst1 | 789 | 10001 | 800 | 135 | +------------+-------+------+-------+------+------+ 3 tuples (4.340ms) sql>select * from tabb; +------------+-------+------+-------+------+------+------+ | aday | key1 | msid | sid | aid | eid | r | +============+=======+======+=======+======+======+======+ | 2016-05-02 | inst1 | 675 | 10001 | 77 | 200 | 88 | | 2016-05-02 | inst1 | 675 | 10002 | 100 | 500 | 112 | | 2016-05-02 | inst1 | 789 | 10001 | 112 | 800 | 134 | +------------+-------+------+-------+------+------+------+ 3 tuples (3.484ms) sql>select * from tabc; +------------+-------+------+-------+------+------+------+ | aday | key1 | msid | sid | aid | eid | m | +============+=======+======+=======+======+======+======+ | 2016-05-02 | inst1 | 675 | 10001 | 77 | 200 | 100 | | 2016-05-02 | inst1 | 675 | 10002 | 100 | 500 | 111 | | 2016-05-02 | inst1 | 789 | 10001 | 112 | 800 | 130 | +------------+-------+------+-------+------+------+------+ 3 tuples (3.215ms) sql>SELECT * more>FROM ( more>SELECT * more> ,sys.DENSE_RANK() OVER (PARTITION BY aday, key1, msid, sid, eid ORDER BY r) as rank more> FROM ( more> (SELECT aday, key1, msid, sid, NULL as aid, eid, 'TABA' as src, r FROM taba) more> UNION more> (SELECT aday, key1, msid, sid, aid, eid, 'TABB' as src, r FROM tabb) more> UNION more> (SELECT aday, key1, msid, sid, aid, m as eid, 'TABC' AS src, r FROM tabc) more> ) AS dummy1 more>) AS dummy2; +------------+-------+------+-------+------+------+------+------+------+ | aday | key1 | msid | sid | aid | eid | src | r | rank | +============+=======+======+=======+======+======+======+======+======+ | 2016-05-02 | inst1 | 675 | 10001 | null | 200 | TABA | 100 | 2 | | 2016-05-02 | inst1 | 675 | 10002 | null | 500 | TABA | 111 | 1 | | 2016-05-02 | inst1 | 789 | 10001 | null | 800 | TABA | 135 | 3 | | 2016-05-02 | inst1 | 675 | 10001 | 77 | 200 | TABB | 88 | 1 | | 2016-05-02 | inst1 | 675 | 10002 | 100 | 500 | TABB | 112 | 2 | | 2016-05-02 | inst1 | 789 | 10001 | 112 | 800 | TABB | 134 | 2 | | 2016-05-02 | inst1 | 675 | 10001 | 77 | 200 | TABC | 100 | 2 | | 2016-05-02 | inst1 | 675 | 10002 | 100 | 500 | TABC | 111 | 1 | | 2016-05-02 | inst1 | 789 | 10001 | 112 | 800 | TABC | 130 | 1 | +------------+-------+------+-------+------+------+------+------+------+ 9 tuples (13.527ms) sql>SELECT aday, key1, msid, sid, aid, eid, src, r, rank more>FROM ( more>SELECT * more> ,sys.DENSE_RANK() OVER (PARTITION BY aday, key1, msid, sid, eid ORDER BY r) as rank more> FROM ( more> (SELECT aday, key1, msid, sid, NULL as aid, eid, 'TABA' as src, r FROM taba) more> UNION more> (SELECT aday, key1, msid, sid, aid, eid, 'TABB' as src, r FROM tabb) more> UNION more> (SELECT aday, key1, msid, sid, aid, m as eid, 'TABC' AS src, r FROM tabc) more> ) AS dummy1 more>) AS dummy2 more>WHERE rank = 1 more>; TypeException:user.s22_3[110]:'calc.dense_rank_grp' undefined in: calc.dense_rank_grp(X_505:date,X_507:str,X_509:int,X_511:int,X_513:lng); program contains errors
Hi Nikola,
I've just managed the reproduce exactly the same which produces and error in the MAL plan.
sql>SELECT aday, key1, msid, sid, aid, eid, src, r, rank more>FROM ( more>SELECT * more> ,sys.DENSE_RANK() OVER (PARTITION BY aday, key1, msid, sid, eid ORDER BY r) as rank more> FROM ( more> (SELECT aday, key1, msid, sid, NULL as aid, eid, 'TABA' as src, r FROM taba) more> UNION more> (SELECT aday, key1, msid, sid, aid, eid, 'TABB' as src, r FROM tabb) more> UNION more> (SELECT aday, key1, msid, sid, aid, m as eid, 'TABC' AS src, r FROM tabc) more> ) AS dummy1 more>) AS dummy2 more>WHERE rank = 1 more>; TypeException:user.s8_1[110]:'calc.dense_rank_grp' undefined in: calc.dense_rank_grp(X_505:date,X_507:str,X_509:int,X_511:int,X_513:lng); program contains errors
Seems like a bug.
Regards,
Brian Hood
On Tue, May 3, 2016 at 12:21 PM, Knezevic Nikola nikkne@gmx.ch wrote:
Hi all,
I have several tables which represent different sources (sensors) of data. I want to find out which sensors picked up the signal first, and thus I decided to join these tables together, rank on common criteria and then select the first ones. However, that fails with:
TypeException:user.s22_3[110]:'calc.dense_rank_grp' undefined in: calc.dense_rank_grp(X_505:date,X_507:str,X_509:int,X_511:int,X_513:lng); program contains errors
Any ideas how to fix this?
Cheers, Nikola
Data: CREATE TABLE taba ( aday DATE, key1 varchar(15), msid int, sid int, eid bigint, r bigint ); CREATE TABLE tabb ( aday DATE, key1 varchar(15), msid int, sid int, aid bigint, eid bigint, r bigint ); CREATE TABLE tabc ( aday DATE, key1 varchar(15), msid int, sid int, aid bigint, m bigint, r bigint );
COPY 3 RECORDS INTO taba FROM stdin USING DELIMITERS '|','\n'; 2016-05-02|inst1|675|10001|200|100 2016-05-02|inst1|675|10002|500|111 2016-05-02|inst1|789|10001|800|135
COPY 3 RECORDS INTO tabb FROM stdin USING DELIMITERS '|','\n'; 2016-05-02|inst1|675|10001|77|200|88 2016-05-02|inst1|675|10002|100|500|112 2016-05-02|inst1|789|10001|112|800|134
COPY 3 RECORDS INTO tabc FROM stdin USING DELIMITERS '|','\n'; 2016-05-02|inst1|675|10001|77|200|100 2016-05-02|inst1|675|10002|100|500|111 2016-05-02|inst1|789|10001|112|800|130
Query:
SELECT aday, key1, msid, sid, aid, eid, src, r, rank FROM ( SELECT * ,sys.DENSE_RANK() OVER (PARTITION BY aday, key1, msid, sid, eid ORDER BY r) as rank FROM ( (SELECT aday, key1, msid, sid, NULL as aid, eid, 'TABA' as src, r FROM taba) UNION (SELECT aday, key1, msid, sid, aid, eid, 'TABB' as src, r FROM tabb) UNION (SELECT aday, key1, msid, sid, aid, m as eid, 'TABC' AS src, r FROM tabc) ) AS dummy1 ) AS dummy2 WHERE rank = 1 ;
Results: sql>CREATE TABLE taba ( more>aday DATE, more>key1 varchar(15), more>msid int, more>sid int, more>eid bigint, more>r bigint more>); operation successful (4.102ms) sql>CREATE TABLE tabb ( more>aday DATE, more>key1 varchar(15), more>msid int, more>sid int, more>aid bigint, more>eid bigint, more>r bigint more>); operation successful (3.172ms) sql>CREATE TABLE tabc ( more>aday DATE, more>key1 varchar(15), more>msid int, more>sid int, more>aid bigint, more>m bigint, more>r bigint more>); operation successful (3.326ms) sql> sql>COPY 3 RECORDS INTO taba FROM stdin USING DELIMITERS '|','\n'; more>2016-05-02|inst1|675|10001|200|100 more>2016-05-02|inst1|675|10002|500|111 more>2016-05-02|inst1|789|10001|800|135 3 affected rows (88.249ms) sql> sql>COPY 3 RECORDS INTO tabb FROM stdin USING DELIMITERS '|','\n'; more>2016-05-02|inst1|675|10001|77|200|88 more>2016-05-02|inst1|675|10002|100|500|112 more>2016-05-02|inst1|789|10001|112|800|134 3 affected rows (59.917ms) sql> sql>COPY 3 RECORDS INTO tabc FROM stdin USING DELIMITERS '|','\n'; more>2016-05-02|inst1|675|10001|77|200|100 more>2016-05-02|inst1|675|10002|100|500|111 more>2016-05-02|inst1|789|10001|112|800|130 3 affected rows (89.804ms) sql> sql>select * from taba; +------------+-------+------+-------+------+------+ | aday | key1 | msid | sid | eid | r | +============+=======+======+=======+======+======+ | 2016-05-02 | inst1 | 675 | 10001 | 200 | 100 | | 2016-05-02 | inst1 | 675 | 10002 | 500 | 111 | | 2016-05-02 | inst1 | 789 | 10001 | 800 | 135 | +------------+-------+------+-------+------+------+ 3 tuples (4.340ms) sql>select * from tabb; +------------+-------+------+-------+------+------+------+ | aday | key1 | msid | sid | aid | eid | r | +============+=======+======+=======+======+======+======+ | 2016-05-02 | inst1 | 675 | 10001 | 77 | 200 | 88 | | 2016-05-02 | inst1 | 675 | 10002 | 100 | 500 | 112 | | 2016-05-02 | inst1 | 789 | 10001 | 112 | 800 | 134 | +------------+-------+------+-------+------+------+------+ 3 tuples (3.484ms) sql>select * from tabc; +------------+-------+------+-------+------+------+------+ | aday | key1 | msid | sid | aid | eid | m | +============+=======+======+=======+======+======+======+ | 2016-05-02 | inst1 | 675 | 10001 | 77 | 200 | 100 | | 2016-05-02 | inst1 | 675 | 10002 | 100 | 500 | 111 | | 2016-05-02 | inst1 | 789 | 10001 | 112 | 800 | 130 | +------------+-------+------+-------+------+------+------+ 3 tuples (3.215ms) sql>SELECT * more>FROM ( more>SELECT * more> ,sys.DENSE_RANK() OVER (PARTITION BY aday, key1, msid, sid, eid ORDER BY r) as rank more> FROM ( more> (SELECT aday, key1, msid, sid, NULL as aid, eid, 'TABA' as src, r FROM taba) more> UNION more> (SELECT aday, key1, msid, sid, aid, eid, 'TABB' as src, r FROM tabb) more> UNION more> (SELECT aday, key1, msid, sid, aid, m as eid, 'TABC' AS src, r FROM tabc) more> ) AS dummy1 more>) AS dummy2; +------------+-------+------+-------+------+------+------+------+------+ | aday | key1 | msid | sid | aid | eid | src | r | rank | +============+=======+======+=======+======+======+======+======+======+ | 2016-05-02 | inst1 | 675 | 10001 | null | 200 | TABA | 100 | 2 | | 2016-05-02 | inst1 | 675 | 10002 | null | 500 | TABA | 111 | 1 | | 2016-05-02 | inst1 | 789 | 10001 | null | 800 | TABA | 135 | 3 | | 2016-05-02 | inst1 | 675 | 10001 | 77 | 200 | TABB | 88 | 1 | | 2016-05-02 | inst1 | 675 | 10002 | 100 | 500 | TABB | 112 | 2 | | 2016-05-02 | inst1 | 789 | 10001 | 112 | 800 | TABB | 134 | 2 | | 2016-05-02 | inst1 | 675 | 10001 | 77 | 200 | TABC | 100 | 2 | | 2016-05-02 | inst1 | 675 | 10002 | 100 | 500 | TABC | 111 | 1 | | 2016-05-02 | inst1 | 789 | 10001 | 112 | 800 | TABC | 130 | 1 | +------------+-------+------+-------+------+------+------+------+------+ 9 tuples (13.527ms) sql>SELECT aday, key1, msid, sid, aid, eid, src, r, rank more>FROM ( more>SELECT * more> ,sys.DENSE_RANK() OVER (PARTITION BY aday, key1, msid, sid, eid ORDER BY r) as rank more> FROM ( more> (SELECT aday, key1, msid, sid, NULL as aid, eid, 'TABA' as src, r FROM taba) more> UNION more> (SELECT aday, key1, msid, sid, aid, eid, 'TABB' as src, r FROM tabb) more> UNION more> (SELECT aday, key1, msid, sid, aid, m as eid, 'TABC' AS src, r FROM tabc) more> ) AS dummy1 more>) AS dummy2 more>WHERE rank = 1 more>; TypeException:user.s22_3[110]:'calc.dense_rank_grp' undefined in: calc.dense_rank_grp(X_505:date,X_507:str,X_509:int,X_511:int,X_513:lng); program contains errors _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Work around but its annoying.
sql>\d rank_test create view rank_test as (select aday, key1, msid, sid, aid, eid, src, r, rank from ( select *, sys.dense_rank() over (partition by aday, key1, msid, sid, eid order by r) as rank from (select aday, key1, msid, sid, null as aid, eid, 'TABA' as src, r from taba union select aday, key1, msid, sid, aid, eid, 'TABB' as src, r from tabb union select aday, key1, msid, sid, aid, m as eid, 'TABC' as src, r from tabc) as dummy1 ) as dummy2); sql>create table rank1 as select * from rank_test; syntax error, unexpected SCOLON, expecting WITH in: "create table rank1 as select * from rank_test;" sql>create table rank1 as select * from rank_test with data; operation successful (40.399ms) sql>select * from rank1; +------------+-------+------+-------+------+------+------+------+------+ | aday | key1 | msid | sid | aid | eid | src | r | rank | +============+=======+======+=======+======+======+======+======+======+ | 2016-05-02 | inst1 | 675 | 10001 | null | 200 | TABA | 100 | 2 | | 2016-05-02 | inst1 | 675 | 10002 | null | 500 | TABA | 111 | 1 | | 2016-05-02 | inst1 | 789 | 10001 | null | 800 | TABA | 135 | 3 | | 2016-05-02 | inst1 | 675 | 10001 | 77 | 200 | TABB | 88 | 1 | | 2016-05-02 | inst1 | 675 | 10002 | 100 | 500 | TABB | 112 | 2 | | 2016-05-02 | inst1 | 789 | 10001 | 112 | 800 | TABB | 134 | 2 | | 2016-05-02 | inst1 | 675 | 10001 | 77 | 200 | TABC | 100 | 2 | | 2016-05-02 | inst1 | 675 | 10002 | 100 | 500 | TABC | 111 | 1 | | 2016-05-02 | inst1 | 789 | 10001 | 112 | 800 | TABC | 130 | 1 | +------------+-------+------+-------+------+------+------+------+------+ 9 tuples (8.079ms) sql>select * from rank1 where rank = 1; +------------+-------+------+-------+------+------+------+------+------+ | aday | key1 | msid | sid | aid | eid | src | r | rank | +============+=======+======+=======+======+======+======+======+======+ | 2016-05-02 | inst1 | 675 | 10002 | null | 500 | TABA | 111 | 1 | | 2016-05-02 | inst1 | 675 | 10001 | 77 | 200 | TABB | 88 | 1 | | 2016-05-02 | inst1 | 675 | 10002 | 100 | 500 | TABC | 111 | 1 | | 2016-05-02 | inst1 | 789 | 10001 | 112 | 800 | TABC | 130 | 1 | +------------+-------+------+-------+------+------+------+------+------+ 4 tuples (3.546ms) sql>
On Wed, May 4, 2016 at 1:01 PM, Brian Hood brianh6854@googlemail.com wrote:
Hi Nikola,
I've just managed the reproduce exactly the same which produces and error in the MAL plan.
sql>SELECT aday, key1, msid, sid, aid, eid, src, r, rank more>FROM ( more>SELECT * more> ,sys.DENSE_RANK() OVER (PARTITION BY aday, key1, msid, sid, eid ORDER BY r) as rank more> FROM ( more> (SELECT aday, key1, msid, sid, NULL as aid, eid, 'TABA' as src, r FROM taba) more> UNION more> (SELECT aday, key1, msid, sid, aid, eid, 'TABB' as src, r FROM tabb) more> UNION more> (SELECT aday, key1, msid, sid, aid, m as eid, 'TABC' AS src, r FROM tabc) more> ) AS dummy1 more>) AS dummy2 more>WHERE rank = 1 more>; TypeException:user.s8_1[110]:'calc.dense_rank_grp' undefined in: calc.dense_rank_grp(X_505:date,X_507:str,X_509:int,X_511:int,X_513:lng); program contains errors
Seems like a bug.
Regards,
Brian Hood
On Tue, May 3, 2016 at 12:21 PM, Knezevic Nikola nikkne@gmx.ch wrote:
Hi all,
I have several tables which represent different sources (sensors) of data. I want to find out which sensors picked up the signal first, and thus I decided to join these tables together, rank on common criteria and then select the first ones. However, that fails with:
TypeException:user.s22_3[110]:'calc.dense_rank_grp' undefined in: calc.dense_rank_grp(X_505:date,X_507:str,X_509:int,X_511:int,X_513:lng); program contains errors
Any ideas how to fix this?
Cheers, Nikola
Data: CREATE TABLE taba ( aday DATE, key1 varchar(15), msid int, sid int, eid bigint, r bigint ); CREATE TABLE tabb ( aday DATE, key1 varchar(15), msid int, sid int, aid bigint, eid bigint, r bigint ); CREATE TABLE tabc ( aday DATE, key1 varchar(15), msid int, sid int, aid bigint, m bigint, r bigint );
COPY 3 RECORDS INTO taba FROM stdin USING DELIMITERS '|','\n'; 2016-05-02|inst1|675|10001|200|100 2016-05-02|inst1|675|10002|500|111 2016-05-02|inst1|789|10001|800|135
COPY 3 RECORDS INTO tabb FROM stdin USING DELIMITERS '|','\n'; 2016-05-02|inst1|675|10001|77|200|88 2016-05-02|inst1|675|10002|100|500|112 2016-05-02|inst1|789|10001|112|800|134
COPY 3 RECORDS INTO tabc FROM stdin USING DELIMITERS '|','\n'; 2016-05-02|inst1|675|10001|77|200|100 2016-05-02|inst1|675|10002|100|500|111 2016-05-02|inst1|789|10001|112|800|130
Query:
SELECT aday, key1, msid, sid, aid, eid, src, r, rank FROM ( SELECT * ,sys.DENSE_RANK() OVER (PARTITION BY aday, key1, msid, sid, eid ORDER BY r) as rank FROM ( (SELECT aday, key1, msid, sid, NULL as aid, eid, 'TABA' as src, r FROM taba) UNION (SELECT aday, key1, msid, sid, aid, eid, 'TABB' as src, r FROM tabb) UNION (SELECT aday, key1, msid, sid, aid, m as eid, 'TABC' AS src, r FROM tabc) ) AS dummy1 ) AS dummy2 WHERE rank = 1 ;
Results: sql>CREATE TABLE taba ( more>aday DATE, more>key1 varchar(15), more>msid int, more>sid int, more>eid bigint, more>r bigint more>); operation successful (4.102ms) sql>CREATE TABLE tabb ( more>aday DATE, more>key1 varchar(15), more>msid int, more>sid int, more>aid bigint, more>eid bigint, more>r bigint more>); operation successful (3.172ms) sql>CREATE TABLE tabc ( more>aday DATE, more>key1 varchar(15), more>msid int, more>sid int, more>aid bigint, more>m bigint, more>r bigint more>); operation successful (3.326ms) sql> sql>COPY 3 RECORDS INTO taba FROM stdin USING DELIMITERS '|','\n'; more>2016-05-02|inst1|675|10001|200|100 more>2016-05-02|inst1|675|10002|500|111 more>2016-05-02|inst1|789|10001|800|135 3 affected rows (88.249ms) sql> sql>COPY 3 RECORDS INTO tabb FROM stdin USING DELIMITERS '|','\n'; more>2016-05-02|inst1|675|10001|77|200|88 more>2016-05-02|inst1|675|10002|100|500|112 more>2016-05-02|inst1|789|10001|112|800|134 3 affected rows (59.917ms) sql> sql>COPY 3 RECORDS INTO tabc FROM stdin USING DELIMITERS '|','\n'; more>2016-05-02|inst1|675|10001|77|200|100 more>2016-05-02|inst1|675|10002|100|500|111 more>2016-05-02|inst1|789|10001|112|800|130 3 affected rows (89.804ms) sql> sql>select * from taba; +------------+-------+------+-------+------+------+ | aday | key1 | msid | sid | eid | r | +============+=======+======+=======+======+======+ | 2016-05-02 | inst1 | 675 | 10001 | 200 | 100 | | 2016-05-02 | inst1 | 675 | 10002 | 500 | 111 | | 2016-05-02 | inst1 | 789 | 10001 | 800 | 135 | +------------+-------+------+-------+------+------+ 3 tuples (4.340ms) sql>select * from tabb; +------------+-------+------+-------+------+------+------+ | aday | key1 | msid | sid | aid | eid | r | +============+=======+======+=======+======+======+======+ | 2016-05-02 | inst1 | 675 | 10001 | 77 | 200 | 88 | | 2016-05-02 | inst1 | 675 | 10002 | 100 | 500 | 112 | | 2016-05-02 | inst1 | 789 | 10001 | 112 | 800 | 134 | +------------+-------+------+-------+------+------+------+ 3 tuples (3.484ms) sql>select * from tabc; +------------+-------+------+-------+------+------+------+ | aday | key1 | msid | sid | aid | eid | m | +============+=======+======+=======+======+======+======+ | 2016-05-02 | inst1 | 675 | 10001 | 77 | 200 | 100 | | 2016-05-02 | inst1 | 675 | 10002 | 100 | 500 | 111 | | 2016-05-02 | inst1 | 789 | 10001 | 112 | 800 | 130 | +------------+-------+------+-------+------+------+------+ 3 tuples (3.215ms) sql>SELECT * more>FROM ( more>SELECT * more> ,sys.DENSE_RANK() OVER (PARTITION BY aday, key1, msid, sid, eid ORDER BY r) as rank more> FROM ( more> (SELECT aday, key1, msid, sid, NULL as aid, eid, 'TABA' as src, r FROM taba) more> UNION more> (SELECT aday, key1, msid, sid, aid, eid, 'TABB' as src, r FROM tabb) more> UNION more> (SELECT aday, key1, msid, sid, aid, m as eid, 'TABC' AS src, r FROM tabc) more> ) AS dummy1 more>) AS dummy2; +------------+-------+------+-------+------+------+------+------+------+ | aday | key1 | msid | sid | aid | eid | src | r | rank | +============+=======+======+=======+======+======+======+======+======+ | 2016-05-02 | inst1 | 675 | 10001 | null | 200 | TABA | 100 | 2 | | 2016-05-02 | inst1 | 675 | 10002 | null | 500 | TABA | 111 | 1 | | 2016-05-02 | inst1 | 789 | 10001 | null | 800 | TABA | 135 | 3 | | 2016-05-02 | inst1 | 675 | 10001 | 77 | 200 | TABB | 88 | 1 | | 2016-05-02 | inst1 | 675 | 10002 | 100 | 500 | TABB | 112 | 2 | | 2016-05-02 | inst1 | 789 | 10001 | 112 | 800 | TABB | 134 | 2 | | 2016-05-02 | inst1 | 675 | 10001 | 77 | 200 | TABC | 100 | 2 | | 2016-05-02 | inst1 | 675 | 10002 | 100 | 500 | TABC | 111 | 1 | | 2016-05-02 | inst1 | 789 | 10001 | 112 | 800 | TABC | 130 | 1 | +------------+-------+------+-------+------+------+------+------+------+ 9 tuples (13.527ms) sql>SELECT aday, key1, msid, sid, aid, eid, src, r, rank more>FROM ( more>SELECT * more> ,sys.DENSE_RANK() OVER (PARTITION BY aday, key1, msid, sid, eid ORDER BY r) as rank more> FROM ( more> (SELECT aday, key1, msid, sid, NULL as aid, eid, 'TABA' as src, r FROM taba) more> UNION more> (SELECT aday, key1, msid, sid, aid, eid, 'TABB' as src, r FROM tabb) more> UNION more> (SELECT aday, key1, msid, sid, aid, m as eid, 'TABC' AS src, r FROM tabc) more> ) AS dummy1 more>) AS dummy2 more>WHERE rank = 1 more>; TypeException:user.s22_3[110]:'calc.dense_rank_grp' undefined in: calc.dense_rank_grp(X_505:date,X_507:str,X_509:int,X_511:int,X_513:lng); program contains errors _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
participants (2)
-
Brian Hood -
Knezevic Nikola