Below you can find the 3 working queries from my script : rank() over (partition by sg.co_societe,sg.id_produit order by sg.date_maj desc,sg.co_type_ref) as rang
RANK(pnvente.dt_debut_commercial, COALESCE(pnvente.id_produit,dem.id_produit)) OVER (PARTITION BY mag.co_magasin, dem.id_produit ORDER BY pnvente.dt_debut_commercial DESC, COALESCE(pnvente.id_produit,dem.id_produit) DESC) as rang RANK(pnvente.dt_debut_commercial, COALESCE(pnvente.id_produit,dem.id_produit)) OVER (PARTITION BY mag.co_magasin, dem.id_produit ORDER BY pnvente.dt_debut_commercial DESC, COALESCE(pnvente.id_produit,dem.id_produit) DESC) as rang 2013/7/19 Edward Capriolo <edlinuxg...@gmail.com> > Please send the fnal working queries. > > > On Friday, July 19, 2013, Jérôme Verdier <verdier.jerom...@gmail.com> > wrote: > > Hi, > > > > The error is resolved. > > > > I have 3 RANK() function in my script. > > > > 2 are working with arguments, but one is working without arguments. > > > > I don't understand it, but the fact is it's working =) > > > > Thanks everyone for your help. > > > > > > 2013/7/18 Jérôme Verdier <verdier.jerom...@gmail.com> > > > > Hi, > > > > Since we saw that we have to give arguments in RANK() function, i'm > trying to translate this one (working on Oracle 10g) to be functionnally in > Hive : > > > > RANK() OVER (PARTITION BY mag.co_magasin, dem.id_produit ORDER BY > pnvente.dt_debut_commercial DESC, > COALESCE(pnvente.id_produit,dem.id_produit) DESC) as rang > > > > i try this : > > > > RANK(pnvente.dt_debut_commercial, > COALESCE(pnvente.id_produit,dem.id_produit)) OVER (PARTITION BY > mag.co_magasin, dem.id_produit ORDER BY pnvente.dt_debut_commercial DESC, > COALESCE(pnvente.id_produit,dem.id_produit) DESC) as rang > > > > and this : > > > > > > RANK(pnvente.dt_debut_commercial, pnvente.id_produit, dem.id_produit) > OVER (PARTITION BY mag.co_magasin, dem.id_produit ORDER BY > pnvente.dt_debut_commercial DESC, > COALESCE(pnvente.id_produit,dem.id_produit) DESC) as rang > > > > But Hive is giving me another error : > > > > FAILED: SemanticException Failed to breakup Windowing invocations into > Groups. At least 1 group must only depend on input columns. Also check for > circular dependencies. > > Underlying error: Ranking Functions can take no arguments > > > > i don't understand this error, in the first try, he said that he can't > work without arguments, and now, rank function is falling because of the > arguments. > > > > what is wrong now ? > > > > > > > > 2013/7/17 Richa Sharma <mailtorichasha...@gmail.com> > > > > my bad ... in relational databases we generally do not give a column > name inside rank() ... but the one in (partition by .... order by..) is > sufficient. > > But looks like that's not the case in Hive > > > > Jerome, > > Please look at the examples in link below. See if you are able to make > it work > > > https://cwiki.apache.org/confluence/display/Hive/LanguageManual+WindowingAndAnalytics#LanguageManualWindowingAndAnalytics-PARTITIONBYwithpartitioning%2CORDERBY%2Candwindowspecification > > > > > > > Cant help you beyond this as i don't have Hive 0.11 :-( > > > > Richa > > > > On Wed, Jul 17, 2013 at 3:08 PM, Jérôme Verdier < > verdier.jerom...@gmail.com> wrote: > > > > Hi Richa, > > > > I have tried one query, with what i've understand of Vijay's tips. > > > > SELECT code_entite, RANK(mag.me_vente_ht) OVER (PARTITION BY > mag.co_societe ORDER BY mag.me_vente_ht) AS rank FROM > default.thm_renta_rgrp_produits_n_1 mag; > > > > This query is working, it gives me results. > > > > You say that maybe i'm hitting the same bug of JIRA HIVE-4663, but query > is also failling when i put analytical columns in... > > > > > > 2013/7/17 Richa Sharma <mailtorichasha...@gmail.com> > > > > Vijay > > Jerome has already passed column - > -- *Jérôme VERDIER* 06.72.19.17.31 verdier.jerom...@gmail.com
