'Row Number () Partition By' syntax problem

General
Enthusiast

'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;

-----------------------------------------------------------------------------------------------------------------------------

8 REPLIES
Enthusiast

Re: 'Row Number () Partition By' syntax problem

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;
Enthusiast

Re: 'Row Number () Partition By' syntax problem

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

Enthusiast

Re: 'Row Number () Partition By' syntax problem

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.

Enthusiast

Re: 'Row Number () Partition By' syntax problem

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;

Enthusiast

Re: 'Row Number () Partition By' syntax problem

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

Enthusiast

Re: 'Row Number () Partition By' syntax problem

Thanks also TD_Raj for writing the statement !!

Enthusiast

Re: 'Row Number () Partition By' syntax problem

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

Senior Apprentice

Re: 'Row Number () Partition By' syntax problem

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