incubator-drill-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Hao Zhu <h...@maprtech.com>
Subject Re: Sub-select
Date Mon, 10 Nov 2014 23:12:26 GMT
Both subquery should work, see below tests in 0.7:

0: jdbc:drill:zk=local> select columns[0],columns[1] from
> dfs.root.`/Users/hzu/Documents/sharefolder/test/sub1.csv`;
> +------------+------------+
> |   EXPR$0   |   EXPR$1   |
> +------------+------------+
> | 1          | a          |
> | 2          | b          |
> | 3          | c          |
> +------------+------------+
> 3 rows selected (0.082 seconds)
> 0: jdbc:drill:zk=local> select columns[0],columns[1] from
> dfs.root.`/Users/hzu/Documents/sharefolder/test/sub2.csv`;
> +------------+------------+
> |   EXPR$0   |   EXPR$1   |
> +------------+------------+
> | 2          | b          |
> | 3          | c          |
> +------------+------------+
> 2 rows selected (0.088 seconds)
> 0: jdbc:drill:zk=local> select columns[1] from
> dfs.root.`/Users/hzu/Documents/sharefolder/test/sub1.csv` where
> . . . . . . . . . . . > columns[0] in (select columns[0] from
> dfs.root.`/Users/hzu/Documents/sharefolder/test/sub2.csv`);
> +------------+
> |   EXPR$0   |
> +------------+
> | b          |
> | c          |
> +------------+
> 2 rows selected (0.586 seconds)
> 0: jdbc:drill:zk=local>
> 0: jdbc:drill:zk=local> select a.columns[1] from
> dfs.root.`/Users/hzu/Documents/sharefolder/test/sub1.csv` as a,
> . . . . . . . . . . . >
> dfs.root.`/Users/hzu/Documents/sharefolder/test/sub2.csv` as b
> . . . . . . . . . . . > where a.columns[0]=b.columns[0];
> +------------+
> |   EXPR$0   |
> +------------+
> | b          |
> | c          |
> +------------+
> 2 rows selected (0.356 seconds)



On Mon, Nov 10, 2014 at 3:02 PM, Richard Shaw <rshaw@maprtech.com> wrote:

> Hi, please can you confirm if the following type of sub-select query is
> possible or how else to go about it.
>
> Case 1:
>
> Select …….
> From fish_data
> Where  “fish_id”  in (select  “fish_id”  from ….. where ….)
>
> Case 2:
>
> Select  …
> From fish_data  fishd
> Join (select … from  where …..)  fishtime   on fishtime.fishid =
> fishd.fishid
>
> Based on an existing query
>
> MS SQL Example:
>
> SELECT TOP 1
>             fishId,
>             datetime AS dataStart
> FROM
>             fish_data
> WHERE
>             fishId = '353234025714222'
>             AND DATEDIFF(
>                          dd,
>                          (SELECT MIN(cake.datetime) AS minDate FROM
> fish_data AS cake WHERE cake.fishId = '353234025714222'),
>                          datetime
>             ) > 5
> ORDER BY
>             datetime ASC
>
> Thank you
>
> Kind Regards,
> Richard
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message