Thanks for the question, Balakumar.
Asked: October 01, 2015 - 9:22 am UTC
Last updated: October 03, 2015 - 1:58 am UTC
Version: 10g
Viewed 1000+ times
You Asked
create table device (lotnumber varchar2(5), device varchar2(5), measuredate date);
BEGIN
insert into device values ('LotA','DevA', TO_DATE('2015/01/10 00:00', 'yyyy/mm/dd hh24:mi'));
insert into device values ('LotA','DevA', TO_DATE('2015/01/10 01:00', 'yyyy/mm/dd hh24:mi'));
insert into device values ('LotA','DevB', TO_DATE('2015/01/10 02:00', 'yyyy/mm/dd hh24:mi'));
insert into device values ('LotB','DevA', TO_DATE('2015/01/10 03:00', 'yyyy/mm/dd hh24:mi'));
insert into device values ('LotB','DevA', TO_DATE('2015/01/10 04:00', 'yyyy/mm/dd hh24:mi'));
insert into device values ('LotA','DevA', TO_DATE('2015/01/10 05:00', 'yyyy/mm/dd hh24:mi'));
END;
/
I want to generate the RowNumber column based on group of LotNumber and Device.As long as the lot and device is the same, the rownumber will be the same. The order is by measuredate. I need output as below format
LotNumber Device MeasureDate RowNumber
LotA DevA 10/1/15 0:00 1
LotA DevA 10/1/15 1:00 1
LotA DevB 10/1/15 2:00 2
LotB DevA 10/1/15 3:00 3
LotB DevA 10/1/15 4:00 3
LotA DevA 10/1/15 5:00 4
I have tried using below query, but i didn't get desired output. can you please help on this?
SELECT * FROM (SELECT LOTNUMBER,DEVICE,TO_CHAR(MEASUREDATE,'DDMMYYYY HH:MM'), DENSE_RANK() OVER(ORDER BY LOTNUMBER, DEVICE) RM FROM DEVICE) ;
and Connor said...
Frstly - thanks for the awesome test case - just cut/paste and I'm ready to roll...I wish everyone could do that.
There are probably better ways to achieve this, but here's a solution
SQL> select
2 lotnumber,
3 device,
4 measuredate,
5 dense_rank() over ( order by rk ) as seq
6 from
7 (
8 select
9 x.*,
10 last_value(r ignore nulls) over ( order by measuredate ) as rk
11 from
12 (
13 select
14 d.*,
15 case
16 when lotnumber != lag(lotnumber) over ( order by measuredate ) or
17 device != lag(device) over ( order by measuredate ) or
18 row_number() over ( order by measuredate ) = 1
19 then
20 row_number() over ( order by measuredate )
21 end as r
22 from device d
23 ) x
24 )
25 order by 3;
LOTNU DEVIC MEASUREDATE SEQ
----- ----- ------------------- ----------
LotA DevA 10/01/2015 00:00:00 1
LotA DevA 10/01/2015 01:00:00 1
LotA DevB 10/01/2015 02:00:00 2
LotB DevA 10/01/2015 03:00:00 3
LotB DevA 10/01/2015 04:00:00 3
LotA DevA 10/01/2015 05:00:00 4
6 rows selected.
Others are welcome to contribute alternatives
Rating
(3 ratings)
Is this answer out of date? If it is, please let us know via a Comment