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:24:45 GMT
Confirmed. The same on 0.6.0.

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

> Please can you confirm the subquery doesn't work on 0.6.0?
>
> Thank you
>
> On 10 November 2014 23:12, Hao Zhu <hzhu@maprtech.com> wrote:
>
>> 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