Home » SQL & PL/SQL » SQL & PL/SQL » Help required in SQL Analytic Funciton (Oracle 11g 11.2.0.1.0)
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
|
|
|
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
|
|
|
|
Goto Forum:
Current Time: Sun May 19 08:50:36 CDT 2024
|