All Forums General
DataHead 34 posts Joined 01/13
13 May 2013
'Row Number () Partition By' syntax problem

Hi,
Can someone tell me why the below won't work? I've tried everything I can think of.
The error I am getting is "expected something like a name or a Unicode delimited identifier or an 'UDFCALLNAME' keyword between ')' and the 'Where' keyword".
Thanks
------------------------------------------------------ ---------------------------------------------
SELECT
Subscription_Id
, Wrls_Prod_Eff_Dt
, Wrls_Prod_Cd
, Wrls_Prod_Ds
FROM
(
SELECT A.*, ROW_NUMBER () OVER (PARTITION BY Subscription_Id ORDER BY Wrls_Prod_Eff_Dt DESC) AS RANK_ID
FROM DW_NLIP_PRV_PLNS A
)
WHERE RANK_ID = 1;
------------------------------------------------------ ------------------------------------------------------ -----------------
 

13 May 2013

Your derived table needs a name.
 

SELECT
Subscription_Id
, Wrls_Prod_Eff_Dt
, Wrls_Prod_Cd
, Wrls_Prod_Ds
FROM
(
SELECT A.*, ROW_NUMBER () OVER (PARTITION BY Subscription_Id ORDER BY Wrls_Prod_Eff_Dt DESC) AS RANK_ID
FROM DW_NLIP_PRV_PLNS A
) AA /* Here is the name */
WHERE RANK_ID = 1;		

 

Thanks & Regards,
Shrinivas Sagare

DataHead 34 posts Joined 01/13
13 May 2013

Thanks but the 'RANK_ID' Column isn't appearing in the result?

13 May 2013

Because you have not added it in SELECT part. What you want to do with RANK_ID in result set? Even if you add it then all values will be 1.

Thanks & Regards,
Shrinivas Sagare

TD_Raj 50 posts Joined 05/10
14 May 2013

SELECT
AA.Subscription_Id
, AA.Wrls_Prod_Eff_Dt
, AA.Wrls_Prod_Cd
, AA.Wrls_Prod_Ds ,AA.RANK_ID
FROM
(
SELECT A.*, ROW_NUMBER () OVER (PARTITION BY Subscription_Id ORDER BY Wrls_Prod_Eff_Dt DESC) AS RANK_ID
FROM DW_NLIP_PRV_PLNS A
)AA
WHERE AA.RANK_ID = 1;

DataHead 34 posts Joined 01/13
14 May 2013

Ah I see, thank you Shrinivas for your help!!

DataHead 34 posts Joined 01/13
14 May 2013

Thanks also TD_Raj for writing the statement !!

Michaelcanady 11 posts Joined 05/13
06 Jun 2013

thanks for this ultimate solution here. i am impressed with it and it works well here for us

..

dnoeth 4628 posts Joined 11/04
07 Jun 2013

Well, the "ultimate solution" in this case would use QUALIFY instead of a Derived Table and WHERE :-)

SELECT *
FROM DW_NLIP_PRV_PLNS
QUALIFY
  ROW_NUMBER () 
  OVER (PARTITION BY Subscription_Id ORDER BY Wrls_Prod_Eff_Dt DESC) = 1;

 
Dieter

Dieter

You must sign in to leave a comment.