To clarify, you want to get this result out?
Val Val1 2738
So for each set of tuples that have the same prop1, you only want to keep the first tuple?
In SQL I think you would use a "LATERAL subquery" to perform a separate GROUP BY + LIMIT / FETCH for each tuple of the outer query, but until we support something similar to that in PGQL I think you may be able to work around by using "scalar subqueries":
SELECT n.prop1 AS prop1 , ( SELECT n.prop2 MATCH (x) WHERE x.prop1 = n.prop1 ORDER BY x.prop3 LIMIT 1 ) AS prop2 , ( SELECT n.prop3 MATCH (y) WHERE y.prop1 = n.prop1 ORDER BY y.prop3 LIMIT 1 ) AS prop3 MATCH (n) GROUP BY n.prop1 ORDER BY prop1, prop3
edit: I updated the query after Gianni found an issue with it. The query should be correct now but the latest PGX has a limitation ("sub-queries are not supported in SELECT clause after GROUP BY") so it will not work a.t.m. unfortunately.
Thanks for the response. In my case prop1 and prop2 are not on same node so I changed the query as below
SELECT n1.prop1 AS prop1
, ( SELECT n2.prop2 MATCH (n11) -(r11)->(n21)WHERE n1.prop1 = n11.prop1 LIMIT 1 ) AS prop2
MATCH (n1) - (r1) -> (n2)
GROUP BY n1.prop1
ORDER BY prop1
I am using PGX3.1.0 to run above query. I am getting below error
21:55:19.404 [pgx-client-thread-2] ERROR oracle.pgx.client.PgxRemoteFuture - caught exception
java.lang.UnsupportedOperationException: sub-queries are not supported in SELECT clause after GROUP BY code: PGX-ERROR-2AWJKDY37Q7AI
at oracle.pgx.common.marshalers.ExceptionMarshaler.toUnserializedException(ExceptionMarshaler.java:77) ~[pgx-api-3.1.1.jar:3.1.1]
at oracle.pgx.common.marshalers.ExceptionMarshaler.unmarshal(ExceptionMarshaler.java:105) ~[pgx-api-3.1.1.jar:3.1.1]
at oracle.pgx.client.RemoteUtils.parseExceptionalResponse(RemoteUtils.java:123) ~[pgx-client-3.1.1.jar:3.1.1]
at oracle.pgx.client.RemoteUtils.getResponseContent(RemoteUtils.java:91) ~[pgx-client-3.1.1.jar:3.1.1]
at oracle.pgx.client.RemoteUtils.parse(RemoteUtils.java:146) ~[pgx-client-3.1.1.jar:3.1.1]
at oracle.pgx.client.PgxRemoteFuture.executeRequest(PgxRemoteFuture.java:105) ~[pgx-client-3.1.1.jar:3.1.1]
at oracle.pgx.client.PgxRemoteFuture.lambda$fetchRemoteResultAsync$16(PgxRemoteFuture.java:181) ~[pgx-client-3.1.1.jar:3.1.1]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [?:1.8.0_131]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [?:1.8.0_131]
at java.lang.Thread.run(Thread.java:748) [?:1.8.0_131]
oracle.pgql.lang.PgqlException: java.util.concurrent.ExecutionException: java.lang.UnsupportedOperationException: sub-queries are not supported in SELECT clause after GROUP BY code: PGX-ERROR-2AWJKDY37Q7AI
PGX 3.1.1 has PGQL 1.1, and the specs says: Subqueries in PGQL 1.1 are limited to existential subqueries. (PGQL 1.1 Specification - 21 December 2017 | PGQL · Property Graph Query Language ).
That's what I didn't posted the same idea as Oskar.
Even if it supported subqueries in other places, your current query couldn't really work: your subquery select n2.prop2, but (n2) is unknown in the subquery as it is an element of the main query. Only n11, n21, r11 could be part of the SELECT of the subquery.
Probably nothing to do with your error anyway ...
Thanks for the information. I am aware that PGQL1.1 is limited to existential sub queries. I am checking if there is an alternate approach to implement my requirement.
Thinking about it ... I guess the EXIST could actually do the job. I need to get a PGX up and test what is crossing my mind ...
>> UnsupportedOperationException: sub-queries are not supported in SELECT clause after GROUP BY
Rigth I forgot about that issue. It is still an unresolved issue in PGX, so scalar subqueries may then unfortunately not be the way to go.
>> Even if it supported subqueries in other places, your current query couldn't really work: your subquery select n2.prop2, but (n2) is unknown in the subquery as it is an element of the main query. Only n11, n21, r11 could be part of the SELECT of the subquery.
Right, my query had a error. I fixed the query now but I'm afraid it will still fail because "sub-queries are not supported in SELECT clause after GROUP BY". Maybe EXISTS could do the job indeed by rewriting it somehow, but I can't immediately think how.
BTW, scalar subqueries are available in PGX 3.1 and up as "extension of PGQL 1.1". They just don't work yet in combination with GROUP BY.
Couldn't get something working ...
Way too spoiled with everything Oracle added on top of standard SQL, all the things which I tried needed something not implemented.