PL/SQL --may be a loop question?

[yongchoi75]

I have two columns, one column with DATE datatype and second column with NUMBER datatype. On third column I want this to be happen.

Column 1 Column 2 Column3
----------- ------------ ------------
02-01-02 1:00pm 1000 1
02-01-02 1:00pm 1000 2
02-01-02 1:00pm 1000 3
02-10-02 2:00pm 2000 1
02-10-02 2:00pm 2000 2
02-14-02 4:00pm 3000 1
02-28-02 5:00pm 4000 1
02-01-02 1:00pm 1000 4

Do you see what's going on. If the rows have same column 1 and column 2 value, I want column 3 to be increment like 1,2,3 etc....

How can this be done when Column 1 datatype and Column 2 datatype is different?

I think I need a procedure here...but I have no clue here...please help...=)

[yongchoi75]

oh this is not clear....

Column 1 supposed to have date and time

Column 2 supposed to have number like 1000,2000, etc..

Column 3 will be the new column that I want to populate...

[chrisrlong]

SELECT
	COL1	,
	COL2	,
	ROW_NUMBER()
		OVER	(
			PARTITION BY
				COL1,
				COL2
			ORDER BY
				???
			)
		AS	COL3
FROM
	T1

- Chris

[yongchoi75]

Got the following error.

select issuedeltas_createdate, problem_link,
row_number over
( partition by issuedeltas_createdata, problem_link order by problem_link)
as newcol from ph_new
/


ERROR at line 3:
ORA-00923: FROM keyword not found where expected

[chrisrlong]

First, this will only work under 8i (8.1.6.2 or later, to be exact)
Second, it will not work in PL/SQL directly - you will have to use Dynamic SQL withing PL/SQL.
Third, you missed the () after the call to the ROW_NUMBER () function.
Fourth, the ORDER BY cannot be on the same column as a PARTITION BY. The question is: Within each partition (where the first 2 columns are the same), how do you want to order the rows? If you don't care, then omit the ORDER BY clause entirely.

HTH,

- Chris

[yongchoi75]

got it. But I have another question. How about in the following case.

Column 1 Column 2 Column 3
02-20-02 1000 1
02-20-02 1000 1
02-20-02 1000 1
02-21-02 2000 2
02-21-02 2000 2
02-22-02 3000 3
02-22-02 3000 3
02-23-02 4000 4


how about this?...

[chrisrlong]

SELECT
	COL1	,
	COL2	,
	RANK()
		OVER	(
			ORDER BY
				COL1,
				COL2
			)
		AS	COL3
FROM
	T1

- Chris

[jmodic]

Change RANK() to DENSE_RANK() in Chris's last example....

[chrisrlong]

Shoot! Had them flipped again! I always do that!

Personally, I think it's Oracle's definitions that are backwards.

- Chris