Something ) Expected Between DESC and RESET

Database
Enthusiast

Something ) Expected Between DESC and RESET

Hi All, 



I have a table as following 



Col1 col2 

A B 

B C 

C D 

P Q 

Q R 

R S 

X Y 

Y Z 

Z F 



Required output: 



Col1 Col2 Col3 

A B D 

B C D 

C D D 

P Q S 

Q R S 

R S S 

X Y F 

Y Z F 

Z F F 



I have tried using this query but getting error. Please suggest me asap. 



SELECT COL1,COL2, 

MIN(CASE WHEN COL2 IN ('D','F','Z') THEN COL2 END) 

OVER(PARTITION BY COL1 

ORDER BY COL2 DESC 

RESET WHEN COL2 IN('D','F','Z') 

ROWS UNBOUNDED PRECEDING) 

FROM 

RECUR_EXAMPLE 

ORDER BY COL2 



Getting error like something ) expected between DESC and RESET 



Please suggest your logic if I am wrong. 



Thanks, 

AmarG

Tags (1)
2 REPLIES
Enthusiast

Re: Something ) Expected Between DESC and RESET

How are you deriving third column (col3) ?

Junior Supporter

Re: Something ) Expected Between DESC and RESET

Hi AmarnathG,

Hope the below query helps. You could try and shorten the same. I have just given it a try.

SELECT
COL1
, COL2
, MAX(COL3_TEST) OVER (PARTITION BY ROW_MOD_SIMILAR ORDER BY ROW_MOD_SIMILAR DESC)
FROM
(
SELECT
COL1
, COL2
, COL3_TEST
, ROW_NUMBER() OVER (PARTITION BY ROW_NUM_MOD_3 ORDER BY COL1,COL2,ROW_NUM_MOD_3 DESC)AS ROW_MOD_SIMILAR
FROM
(
SELECT
COL1
, COL2
, ROW_NUMBER() OVER (PARTITION BY NULL ORDER BY COL1,COL2 DESC) MOD 3 AS ROW_NUM_MOD_3
, CASE
WHEN ROW_NUM_MOD_3 = 0
THEN COL2
ELSE NULL
END AS COL3_TEST
FROM
RECUR_EXAMPLE
) A
) B
ORDER BY 1,2;

Thanks,

Rohan Sawant