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

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


Copy link to this message
-
Re: Invalid Function rank in HiveQL
Yup this works. Thanks for the help.

*Raihan Jamal*

On Tue, Jul 10, 2012 at 4:37 PM, Vijay <[EMAIL PROTECTED]> wrote:

> 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,