Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

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

Comments

Useful

Balakumar S, October 01, 2015 - 1:30 pm UTC

Thanks for the response. could you please provide some simple query to archive?

The MODEL Boy !

Rajeshwaran Jeyabal, October 01, 2015 - 2:42 pm UTC

rajesh@ORA10G>
rajesh@ORA10G>
rajesh@ORA10G> select *
  2  from device t
  3  model
  4    dimension by ( row_number() over(order by measuredate) rn )
  5    measures( lotnumber, device, measuredate, 0 rnk )
  6    rules
  7    ( rnk[rn=1] = 1 ,
  8      rnk[rn>1] order by rn = case when lotnumber[cv()-1] <> lotnumber[cv()]
  9                      or device[cv()-1] <> device[cv()] then rnk[cv()-1]+1 else
 10                             rnk[cv()-1] end )
 11  /

        RN LOTNU DEVIC MEASUREDATE        RNK
---------- ----- ----- ----------- ----------
         1 LotA  DevA  10-JAN-2015          1
         2 LotA  DevA  10-JAN-2015          1
         3 LotA  DevB  10-JAN-2015          2
         4 LotB  DevA  10-JAN-2015          3
         5 LotB  DevA  10-JAN-2015          3
         6 LotA  DevA  10-JAN-2015          4

6 rows selected.

rajesh@ORA10G> set autotrace traceonly explain
rajesh@ORA10G> /

Execution Plan
----------------------------------------------------------
Plan hash value: 2638759780

------------------------------------------------------------------------------
| Id  | Operation           | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |        |     6 |   108 |     4  (25)| 00:00:01 |
|   1 |  SQL MODEL ORDERED  |        |     6 |   108 |     4  (25)| 00:00:01 |
|   2 |   WINDOW SORT       |        |     6 |   108 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| DEVICE |     6 |   108 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

rajesh@ORA10G> set autotrace off
rajesh@ORA10G>

One variant and one alternative

Stew Ashton, October 02, 2015 - 6:01 am UTC

This is simply a variant of Connor's solution, which some call the "start of group" method.
with start_of_group as (
  select a.*,
  case when lotnumber = lag(lotnumber) over(order by measuredate)
    and device = lag(device) over(order by measuredate)
    then 0 else 1
  end grp_start
  from device a
)
select a.*,
sum(grp_start) over(order by measuredate) grp_id
from start_of_group a;

LOTNU DEVIC MEASUREDATE       GRP_START     GRP_ID
----- ----- ---------------- ---------- ----------
LotA  DevA  2015-01-10 00:00          1          1
LotA  DevA  2015-01-10 01:00          0          1
LotA  DevB  2015-01-10 02:00          1          2
LotB  DevA  2015-01-10 03:00          1          3
LotB  DevA  2015-01-10 04:00          0          3
LotA  DevA  2015-01-10 05:00          1          4
If and when you get version 12c, you can always replace the "start of group" method with the MATCH_RECOGNIZE clause:
select * from device
match_recognize(
  order by measuredate
  measures match_number()mn
  all rows per match
  pattern(a b*)
  define b as lotnumber = prev(lotnumber) and device = prev(device)
);

MEASUREDATE              MN LOTNU DEVIC
---------------- ---------- ----- -----
2015-01-10 00:00          1 LotA  DevA 
2015-01-10 01:00          1 LotA  DevA 
2015-01-10 02:00          2 LotA  DevB 
2015-01-10 03:00          3 LotB  DevA 
2015-01-10 04:00          3 LotB  DevA 
2015-01-10 05:00          4 LotA  DevA

Connor McDonald
October 03, 2015 - 1:58 am UTC

Thanks Stew. Always top notch

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.