Hi Raihan, You should use 'rank(buyer_id)' in the order by clause on line 9 in stead of the alias 'rk'. I had the same problem, strangely, the alias is not resolved when it is in the order by clause.
Other thing, I had some issues when I used this exact same set up for ranking results, that the rank UDF was already implemented at the map phase. Then you get very strange results. You have to introduce an extra subquery for it to work. Jasper Op 10 jul. 2012 09:01 schreef "Raihan Jamal" <jamalrai...@gmail.com> het volgende: > I was not able to understand, This is my below qiuery that I am using > currently- > > SELECT buyer_id, item_id, created_time > FROM ( > SELECT buyer_id, item_id, rank(buyer_id) as rank, created_time > FROM testingtable1 > DISTRIBUTE BY buyer_id, item_id > SORT BY buyer_id, item_id, created_time desc > ) a > WHERE rank < 10 > ORDER BY buyer_id, created_time, rank; > > > What changes I need to make? > > > > > *Raihan Jamal* > > > > On Mon, Jul 9, 2012 at 11:52 PM, Nitin Pawar <nitinpawar...@gmail.com>wrote: > >> try rk in upper select statement as well >> >> >> On Tue, Jul 10, 2012 at 12:12 PM, Raihan Jamal <jamalrai...@gmail.com>wrote: >> >>> Thanks for commenting out. Yes I figured that out, its a UDF. So now I >>> have created a new UDF Rank and added to classpath also. But when I am >>> again running the below query- >>> >>> SELECT buyer_id, item_id, created_time >>> FROM ( >>> SELECT buyer_id, item_id, Rank(buyer_id) as rk, created_time >>> FROM testingtable1 >>> DISTRIBUTE BY buyer_id, item_id >>> SORT BY buyer_id, item_id, created_time desc >>> ) a >>> WHERE rk < 10 >>> ORDER BY buyer_id, rk; >>> >>> I am getting this below error- >>> >>> *FAILED: Error in semantic analysis: line 9:19 Invalid Table Alias or >>> Column Reference rk* >>> >>> >>> Why is it so? Any suggestions? >>> >>> >>> *Raihan Jamal* >>> >>> >>> >>> On Mon, Jul 9, 2012 at 10:51 PM, Vijay <tec...@gmail.com> wrote: >>> >>>> hive has no built-in rank function. you'd need to use a user-defined >>>> function (UDF) to simulate it. there are a few custom implementations >>>> on the net that you can leverage. >>>> >>>> On Mon, Jul 9, 2012 at 10:40 PM, Raihan Jamal <jamalrai...@gmail.com> >>>> wrote: >>>> > What's wrong with the below query. >>>> > >>>> > >>>> > SELECT buyer_id, item_id, created_time >>>> > FROM ( >>>> > SELECT buyer_id, item_id, rank(buyer_id) as rank, created_time >>>> > FROM testingtable1 >>>> > DISTRIBUTE BY buyer_id, item_id >>>> > SORT BY buyer_id, item_id, created_time desc >>>> > ) a >>>> > WHERE rank < 10 >>>> > ORDER BY buyer_id, rank >>>> > >>>> > >>>> > I am always getting as - >>>> > >>>> > FAILED: Error in semantic analysis: line 3:30 Invalid Function rank >>>> > >>>> > >>>> > I am using Hive 0.6 I guess. >>>> > >>>> > >>>> > >>>> > Raihan Jamal >>>> > >>>> >>> >>> >> >> >> -- >> Nitin Pawar >> >> >
