Works for me. WITH X1 AS (SELECT city, AVG(review_count) AS city_reviews_avg FROM `yelp_academic_dataset_business.json` GROUP BY city), X2 AS (SELECT X1.city, X1.city_reviews_avg, MAX(X1.city_reviews_avg) OVER () AS city_reviews_avg_max FROM X1) SELECT X2.city, X2.city_reviews_avg FROM X2 WHERE X2.city_reviews_avg_max = X2.city_reviews_avg;
+------------+-------------------+ | city | city_reviews_avg | +------------+-------------------+ | Summerlin | 128.0 | +------------+-------------------+ 1 row selected (0.87 seconds) On Wed, Sep 23, 2015 at 4:27 PM, Jinfeng Ni <[email protected]> wrote: > Is it something related to the json file you used? > > I tried the following query, replacing "business.json' with > cp.`employee.json`. It works fine. (no row returned, which is > expected). > > 0: jdbc:drill:zk=local> WITH X1 > . . . . . . . . . . . > AS > . . . . . . . . . . . > (SELECT city, > . . . . . . . . . . . > AVG(review_count) AS city_reviews_avg > . . . . . . . . . . . > FROM cp.`employee.json` > . . . . . . . . . . . > GROUP BY city), > . . . . . . . . . . . > X2 > . . . . . . . . . . . > AS > . . . . . . . . . . . > (SELECT X1.city, X1.city_reviews_avg, > . . . . . . . . . . . > MAX(X1.city_reviews_avg) > . . . . . . . . . . . > OVER () AS city_reviews_avg_max > . . . . . . . . . . . > FROM X1) > . . . . . . . . . . . > SELECT X2.city, X2.city_reviews_avg > . . . . . . . . . . . > FROM X2 > . . . . . . . . . . . > WHERE X2.city_reviews_avg_max = > X2.city_reviews_avg; > +-------+-------------------+ > | city | city_reviews_avg | > +-------+-------------------+ > +-------+-------------------+ > No rows selected (3.462 seconds) > > > On Wed, Sep 23, 2015 at 4:23 PM, Bob Rumsby <[email protected]> wrote: > > Based on our docs, multiple table references are valid, and this is > > consistent with other systems where WITH is supported in queries. > However, > > maybe the problem is the reference to X1 in the definition of X2? > > > > Bob > > > > On Wed, Sep 23, 2015 at 3:41 PM, Neeraja Rentachintala < > > [email protected]> wrote: > > > >> Team > >> Is this a valid Drill syntax (i.e 2 tables in with clause). > >> > >> WITH X1 > >> AS > >> (SELECT city, > >> AVG(review_count) AS city_reviews_avg > >> FROM `business.json` > >> GROUP BY city), > >> X2 > >> AS > >> (SELECT X1.city, X1.city_reviews_avg, > >> MAX(X1.city_reviews_avg) > >> OVER () AS city_reviews_avg_max > >> FROM X1) > >> SELECT X2.city, X2.city_reviews_avg > >> FROM X2 > >> WHERE X2.city_reviews_avg_max = X2.city_reviews_avg; > >> > >> I am hitting this error. *Error: SYSTEM ERROR: NoSuchFieldError: > constants* > >> > >> A single table specification in WITH clause works fine. > >> > >> -Neeraja > >> >
