Home | About | Sematext search-lucene.com search-hadoop.com
 Search Hadoop and all its subprojects:

Switch to Plain View
Hive >> mail # user >> Invalid Function rank in HiveQL


+
Raihan Jamal 2012-07-10, 05:40
+
Vijay 2012-07-10, 05:51
+
Raihan Jamal 2012-07-10, 06:42
+
Nitin Pawar 2012-07-10, 06:52
+
Raihan Jamal 2012-07-10, 07:00
+
Nitin Pawar 2012-07-10, 07:04
+
Jasper Knulst 2012-07-10, 07:16
+
Raihan Jamal 2012-07-10, 19:31
+
Vijay 2012-07-10, 23:01
+
Raihan Jamal 2012-07-10, 23:18
+
Raihan Jamal 2012-07-10, 23:21
Copy link to this message
-
Re: Invalid Function rank in HiveQL
In that case, wouldn't this work:

SELECT buyer_id, item_id, rank(buyer_id), created_time
FROM (
    SELECT buyer_id, item_id, created_time
    FROM testingtable1
    DISTRIBUTE BY buyer_id
    SORT BY buyer_id, created_time desc
) a
WHERE rank(buyer_id) < 10;
On Tue, Jul 10, 2012 at 4:21 PM, Raihan Jamal <[EMAIL PROTECTED]> wrote:
> So the OUTPUT should be like this everything sorted in descending order with
> time and only top 10 for each BUYER_ID-
>
> Sample Output.
>
> BUYER_ID    |    ITEM_ID       |    CREATED_TIME
>
>
> ------------+------------------+-----------------------
>
>
>
> 34512201        2412180494          2012-07-09 22:21:11
>
>
> 34512201        1875744030          2012-07-09 19:40:39
>
>
> 34512201        597245693           2012-07-09 16:20:21
>
>
> 34512201        8071787728          2012-07-09 15:19:59
>
>
> 34512201        959188449           2012-07-09 08:48:47
>
>
> 34512201        5868222883          2012-07-09 08:23:17
>
>
> 34512201        5656232360          2012-07-09 07:28:47
>
>
> 34512201        5639158173          2012-07-09 06:58:47
>
>
> 34512201        4645350592          2012-07-09 06:38:47
>
>
> 34512201        2422054205          2012-07-09 06:09:56
>
>
> 1015826235      210002448035        2012-07-09 22:21:11
>
>
> 1015826235      260003553382        2012-07-09 19:40:39
>
>
> 1015826235      220003038067        2012-07-09 19:40:21
>
>
> 1015826235      300003861266        2012-07-09 18:19:59
>
>
> 1015826235      140002997245        2012-07-09 09:23:17
>
>
> 1015826235      260003553385        2012-07-09 08:48:47
>
>
> 1015826235      260003553384        2012-07-09 07:28:47
>
>
> 1015826235      260003553381        2012-07-09 07:09:56
>
>
> 1015826235      260003553383        2012-07-09 06:58:47
>
>
> 1015826235      260003553389        2012-07-09 06:54:37
>
>
>
>
>
> Raihan Jamal
>
>
>
> On Tue, Jul 10, 2012 at 4:18 PM, Raihan Jamal <[EMAIL PROTECTED]> wrote:
>>
>> Thanks Vijay for reply. But it doesn't works out the way I needed. I am
>> getting full data back for each BUYER_ID. Let me explain you more.
>>
>> This is the below data in the table and you can see in the below data this
>> BUYER_ID - 1015826235 appears 13 times and this BUYER_ID -34512201 appears
>> 12 times, And I need TOP 10 for each of the BUYER_ID BASIS on time-
>>
>> BUYER_ID    |    ITEM_ID       |    CREATED_TIME
>>
>>
>>
>> ------------+------------------+-----------------------
>>
>>
>>
>> 1015826235      220003038067        2012-07-09 19:40:21,
>>
>>
>>
>> 1015826235      300003861266        2012-07-09 18:19:59,
>>
>>
>>
>> 1015826235      140002997245        2012-07-09 09:23:17,
>>
>>
>>
>> 1015826235      210002448035        2012-07-09 22:21:11,
>>
>>
>>
>> 1015826235      260003553381        2012-07-09 07:09:56,
>>
>>
>>
>> 1015826235      260003553382        2012-07-09 19:40:39,
>>
>>
>>
>> 1015826235      260003553383        2012-07-09 06:58:47,
>>
>>
>>
>> 1015826235      260003553384        2012-07-09 07:28:47,
>>
>>
>>
>> 1015826235      260003553385        2012-07-09 08:48:47,
>>
>>
>>
>> 1015826235      260003553386        2012-07-09 06:38:47,
>>
>>
>>
>> 1015826235      260003553387        2012-07-09 05:38:47,
>>
>>
>>
>> 1015826235      260003553388        2012-07-09 04:55:47,
>>
>>
>>
>> 1015826235      260003553389        2012-07-09 06:54:37,
>>
>>
>>
>> 34512201        597245693           2012-07-09 16:20:21,
>>
>>
>>
>> 34512201        8071787728          2012-07-09 15:19:59,
>>
>>
>>
>> 34512201        5868222883          2012-07-09 08:23:17,
>>
>>
>>
>> 34512201        2412180494          2012-07-09 22:21:11,
>>
>>
>>
>> 34512201        2422054205          2012-07-09 06:09:56,
>>
>>
>>
>> 34512201        1875744030          2012-07-09 19:40:39,
>>
>>
>>
>> 34512201        5639158173          2012-07-09 06:58:47,
>>
>>
>>
>> 34512201        5656232360          2012-07-09 07:28:47,
>>
>>
>>
>> 34512201        959188449           2012-07-09 08:48:47,
>>
>>
>>
>> 34512201        4645350592          2012-07-09 06:38:47,
+
Raihan Jamal 2012-07-11, 03:31