All Forums Database
AmarnathG 16 posts Joined 10/12
23 Dec 2014
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

AmarnathG
Tags:
mnagara 6 posts Joined 11/14
24 Dec 2014

How are you deriving third column (col3) ?

Thanks,
Mikhil

Rohan_Sawant 55 posts Joined 07/14
24 Dec 2014

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

 

You must sign in to leave a comment.