CODEWITHSUNDEEP
oracle-databaseoracle-sqldeveloper
daniel
daniel

Reputation: 1

ROW number over a group with ordering. Oracle SQL

 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 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.

Upvotes: 0

Views: 5466

Answers (3)

Bala S
Bala S

Reputation: 523

Use Dense_rank to get desired output.
create table device (lotnumber varchar2(5), device varchar2(5), measuredate date);
insert into device values ('LotA','DevA', sysdate);
insert into device values ('LotA','DevA', sysdate);
insert into device values ('LotA','DevB', sysdate);
insert into device values ('LotB','DevA', sysdate);
insert into device values ('LotB','DevA', sysdate);
insert into device values ('LotA','DevA', sysdate);

Use below query to get desired output.
SELECT * FROM (SELECT DEVICE.*, DENSE_RANK() OVER(ORDER BY LOTNUMBER, DEVICE) RM FROM DEVICE) ;

Upvotes: 0

brenners1302
brenners1302

Reputation: 1478

If you just want to generate rownumbers the same as what is displayed. You can try this 

SELECT Lotnumber, 
       Device, 
       MeasureDate, 
       DENSE_RANK() OVER (ORDER BY MEASURE DATE ) AS RowNumber 
FROM TABLE

Upvotes: 0

sstan
sstan

Reputation: 36483

This can be done by using the LAG analytic function in an inline view, followed by a cumulative sum to only sum when the values differ from the previous row.

select lot_number,
       device,
       measure_date
       sum(case when last_lot_number = lot_number and last_device = device
                then 0
                else 1 end) over (order by measure_date) as row_number
  from (select lot_number,
               device,
               measure_date,
               lag(lot_number) over(order by measure_date) as last_lot_number,
               lag(device) over(order by measure_date) as last_device
          from your_table)
order by measure_date

SQLFiddle Demo

Upvotes: 1

Related Questions