OraFAQ Forum: SQL & PL/SQL » Help required in SQL Analytic Funciton

Home » SQL & PL/SQL » SQL & PL/SQL » Help required in SQL Analytic Funciton (Oracle 11g 11.2.0.1.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Help required in SQL Analytic Funciton [message #655436]

Wed, 31 August 2016 04:09

jimit_shaili
Messages: 237
Registered: June 2006
Location: India, Ahmedabad

Senior Member

Dear All,

Here is my test case.

CREATE TABLE TEST
(
  ID         NUMBER(8),
  TRAN_DATE  DATE,
  TRUCK_NO   VARCHAR2(30 BYTE)
);

Table created.

Insert into TEST   (ID, TRAN_DATE, TRUCK_NO) Values   (1, TO_DATE('04/02/2016 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'TRUCK1');
Insert into TEST   (ID, TRAN_DATE, TRUCK_NO) Values   (2, TO_DATE('04/04/2016 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'TRUCK2');
Insert into TEST   (ID, TRAN_DATE, TRUCK_NO) Values   (3, TO_DATE('04/04/2016 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'TRUCK2');
Insert into TEST   (ID, TRAN_DATE, TRUCK_NO) Values   (4, TO_DATE('04/05/2016 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'TRUCK3');
Insert into TEST   (ID, TRAN_DATE, TRUCK_NO) Values   (5, TO_DATE('04/06/2016 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'TRUCK1');

commit;

select id,tran_date,truck_no
,row_number() over(partition by tran_date,truck_no order by tran_date,truck_no) rn
from test;

 ID TRAN_DATE TRUCK_NO                               RN
--- --------- ------------------------------ ----------
  1 02-APR-16 TRUCK1                                  1
  2 04-APR-16 TRUCK2                                  1
  3 04-APR-16 TRUCK2                                  2
  4 05-APR-16 TRUCK3                                  1
  5 06-APR-16 TRUCK1                                  1

I would like have result like this. My criteria will be truck_no to serialized tran_date plus truck_no. Require output can be like below.

 ID TRAN_DATE TRUCK_NO                               RN
--- --------- ------------------------------ ----------
  1 02-APR-16 TRUCK1                                  1
  2 04-APR-16 TRUCK2                                  2
  3 04-APR-16 TRUCK2                                  2
  4 05-APR-16 TRUCK3                                  3
  5 06-APR-16 TRUCK1                                  4

Please correct me in my try or suggest some other way around.

Thanks & Regards

Jimit

Report message to a moderator

Re: Help required in SQL Analytic Funciton [message #655444 is a reply to message #655436]

Wed, 31 August 2016 05:18

jimit_shaili
Messages: 237
Registered: June 2006
Location: India, Ahmedabad

Senior Member

Dear All

This may be a solution, please guide if this may/can lead to a problem if combination data get complex.

select id,tran_date,truck_no
,dense_rank() over(order by tran_date,truck_no) row_num
from test
/

ID TRAN_DATE TRUCK_NO                          ROW_NUM
-- --------- ------------------------------ ----------
 1 02-APR-16 TRUCK1                                  1
 2 04-APR-16 TRUCK2                                  2
 3 04-APR-16 TRUCK2                                  2
 4 05-APR-16 TRUCK3                                  3
 5 06-APR-16 TRUCK1                                  4

Thanks & Regards

Jimit

Report message to a moderator

Re: Help required in SQL Analytic Funciton [message #655464 is a reply to message #655436]

Wed, 31 August 2016 06:55

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

My criteria will be truck_no to serialized tran_date plus truck_no.

What does this mean?
Explain each line of your result.

Report message to a moderator

Previous Topic:	Insert data into SYSTEM partitioned table using DB link
Next Topic:	finding previous quarter end date

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 06:00:07 CDT 2024