8 Replies Latest reply on Jun 6, 2019 4:50 PM by Gianni Ceresa

    How to get first record of GROUP BY PGQL statement

    mohanr.k

      I need to find the first record of GROUP BY statement. Want to know if there is any function or some other way to implement this. My Query is as follows

      GROUP BY prop1 ,prop2,prop3

      ORDER BY prop1 ,prop3

       

      My data looks as below and I want to display the count as 1 because prop1 column value is same for all 3 rows.

       

      prop1prop2prop3
      ValVal12738
      ValVal28982
      ValVal39837
        • 1. Re: How to get first record of GROUP BY PGQL statement
          oskarvanrest-Oracle

          Hi mohanr.k,

           

          To clarify, you want to get this result out?

           

           

          prop1

          prop2

          prop3

          ValVal12738

           

          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.

          • 2. Re: How to get first record of GROUP BY PGQL statement
            mohanr.k

            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

            • 3. Re: How to get first record of GROUP BY PGQL statement
              Gianni Ceresa

              Hi,

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

              • 4. Re: How to get first record of GROUP BY PGQL statement
                mohanr.k

                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.

                • 5. Re: How to get first record of GROUP BY PGQL statement
                  Gianni Ceresa

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

                  • 6. Re: How to get first record of GROUP BY PGQL statement
                    oskarvanrest-Oracle

                    @mohanr.k:

                     

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

                     

                     

                    @Gianni:

                     

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

                    • 7. Re: How to get first record of GROUP BY PGQL statement
                      mohanr.k

                      @oskarvanrest-Oracle

                       

                      Thanks for your response. Whenever you have time, please check and let me know if this can be achieved using EXISTS

                      • 8. Re: How to get first record of GROUP BY PGQL statement
                        Gianni Ceresa

                        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.