-
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...=)
-
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...
-
Code:
SELECT
COL1 ,
COL2 ,
ROW_NUMBER()
OVER (
PARTITION BY
COL1,
COL2
ORDER BY
???
)
AS COL3
FROM
T1
- Chris
-
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
-
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
-
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?...
-
Code:
SELECT
COL1 ,
COL2 ,
RANK()
OVER (
ORDER BY
COL1,
COL2
)
AS COL3
FROM
T1
- Chris
-
Change RANK() to DENSE_RANK() in Chris's last example....
-
Shoot! Had them flipped again! I always do that!
Personally, I think it's Oracle's definitions that are backwards. ;)
- Chris
