Ok,
At any rate, you could replace your dummy join by a cross join:
SELECT
nfr.score
FROM student nfr
LEFT OUTER JOIN
(
SELECT
a.score as fra
FROM student a
CROSS JOIN student b
WHERE a.score < b.score
) frab
ON
frab.fra = nfr.score
WHERE
frab.fra is null and nfr.score is not null
;
Regards,
Furcy
2014-04-23 16:49 GMT+02:00 Sanjay Subramanian <[email protected]>:
> Thanks
> For the sake of this question I wanted to avoid all order by and limit
> syntax 😄. It's more of a challenge question
> Regards
> Sanjay
>
> Sent from my iPhone
>
> On Apr 23, 2014, at 2:51 AM, Furcy Pin <[email protected]> wrote:
>
> Hi,
>
> note that if your table contains the max value several time, all the
> occurences will be returned. Note also that if it contains a null it will
> be returned too.
>
> How about this?
>
> SELECT score FROM student ORDER BY score DESC LIMIT 1 ;
>
>
> Note that on this query Impala is incoherent with Hive or mySQL, since
> Impala considers
> that NULL is *greater* than everything while Hive and mySQL consider NULL
> to be *smaller* than everything
>
> And just for fun, you can also try in Hive:
>
> FROM
> (
> SELECT score FROM student
> DISTRIBUTE BY '1'
> SORT BY score DESC
> ) M
> SELECT score
> LIMIT 1
> ;
>
> FROM
> (
> SELECT score FROM student
> DISTRIBUTE BY '1'
> ) M
> REDUCE score USING 'sort -rn | head -1' AS score
> ;
>
> The second is just to demonstrate the possibilities of custom reduce, but
> is greatly inefficient (in speed and memory).
> And be sure to use SET mapred.reduce.tasks=1 ; before if you don't want
> idle reduce tasks...
>
> (I guess I have some spare time too!)
>
>
>
> 2014-04-23 3:27 GMT+02:00 Subramanian, Sanjay (HQP) <
> [email protected]>:
>
>> Hey guys
>>
>> TABLE=STUDENT
>> COLUMN=SCORE
>>
>> U want to find the max value in the column without using any
>> aggregation functions.
>>
>> Its easy in a RDB context but I was trying to get a solution in Hive
>> (clearly I have some spare time on my hands - LOL)
>>
>> select
>> nfr.score
>> from
>> student nfr
>> left outer join
>> (select
>> a.score as fra,
>> b.score as frb
>> from
>> (select
>> '1' as dummy,
>> score
>> from
>> student
>> ) a
>>
>> join
>> (select
>> '1' as dummy,
>> score
>> from
>> student
>> ) b
>> ON
>> a.dummy = b.dummy
>> where
>> a.score < b.score
>> ) frab
>> on
>> frab.fra=nfr.score
>> where
>> frab.fra is null
>>
>> Thanks
>>
>> Warm Regards
>>
>>
>> Sanjay
>>
>>
>>
>
