Home » SQL & PL/SQL » SQL & PL/SQL » Same serial for a group (Oracle 11g)
| Same serial for a group [message #639405] |
Mon, 06 July 2015 15:57  |
 |
vspn
Messages: 10
Registered: July 2015
|
Junior Member |
|
|
I have data as below in the table.
Facility Store Stop
620592 S0722 1
620578 S0308 2
620578 S0631 3
620590 S0678 4
620590 S0444 5
620590 S0237 6
I need Sequence number for these record as
Facility Stop Sequence
620592 1 1
620578 2 2
620578 3 2
620590 4 3
620590 5 3
620590 6 3
Here Sequence should be based on group by Facility and order by Stop. Please help in creating sql query for this.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Same serial for a group [message #639539 is a reply to message #639489] |
Thu, 09 July 2015 06:10  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
vspn wrote on Wed, 08 July 2015 20:46I tried ROW_NUMBER it is not giving expected results.
Use COUNT().
SQL> with data(Facility, Store, Stop ) as(
2 select 620592, 'S0722', 1 from dual union all
3 select 620578, 'S0308', 2 from dual union all
4 select 620578, 'S0631', 3 from dual union all
5 select 620590, 'S0678', 4 from dual union all
6 select 620590, 'S0444', 5 from dual union all
7 select 620590, 'S0237', 6 from dual
8 )
9 select t.*, count(facility) over(partition by facility) rn
10 from data t
11 order by stop;
FACILITY STORE STOP RN
---------- ----- ---------- ----------
620592 S0722 1 1
620578 S0308 2 2
620578 S0631 3 2
620590 S0678 4 3
620590 S0444 5 3
620590 S0237 6 3
6 rows selected.
SQL>
And please use code tags when you post your code. Instead of copy pasting the data, you should provide the create and insert statements, or at least the data set using WITH clause just like I did. Without this, the entire work needs to be done by the one who wants to help you, which could be annoying. Please do this in future, welcome to the forum!
Regards,
Lalit
|
|
|
|
| Re: Same serial for a group [message #639540 is a reply to message #639538] |
Thu, 09 July 2015 06:13 |
 |
Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Lalit Kumar B wrote on Thu, 09 July 2015 13:07Michel Cadot wrote on Tue, 07 July 2015 10:32
ROW_NUMBER and COUNT analytic functions will do the trick.
Only COUNT() analytic function would suffice.
Yes, I didn't see STOP is in the source table (and even if it was not, we can do it with 2 COUNT).
[Updated on: Thu, 09 July 2015 06:14] Report message to a moderator |
|
|
|
|
|
| Re: Same serial for a group [message #639543 is a reply to message #639542] |
Thu, 09 July 2015 06:25 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
|
I didn't want to ruin his learning. I see he has shown what he tried(ROW_NUMBER), and I see you gave him *hints*. I thought he is getting confused, and now he might play around with the solution and understand why his query didn't work. Also, as he was not posting the test case, I thought of showing him how to build the data set which could be used by others using WITH clause. I just gave him one chance, if he continues not to follow the guidelines(which I don't expect him to do) then obviously I won't help him at all.
|
|
|
|
|
|
| Re: Same serial for a group [message #639548 is a reply to message #639544] |
Thu, 09 July 2015 08:19 |
|
Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Note: what about
SQL> with data(Facility, Store, Stop ) as(
2 select 620592, 'S0722', 1 from dual union all
3 select 620578, 'S0308', 2 from dual union all
4 select 620578, 'S0631', 3 from dual union all
5 select 620590, 'S0678', 4 from dual union all
6 select 620590, 'S0444', 5 from dual union all
7 select 620590, 'S0237', 6 from dual union all
8 select null, 'S0238', 7 from dual union all
9 select null, 'S0239', 8 from dual
10 )
11 select t.*, count(facility) over(partition by facility) rn
12 from data t
13 order by stop;
FACILITY STORE STOP RN
---------- ----- ---------- ----------
620592 S0722 1 1
620578 S0308 2 2
620578 S0631 3 2
620590 S0678 4 3
620590 S0444 5 3
620590 S0237 6 3
S0238 7 0
S0239 8 0
|
|
|
|
|
|
| Re: Same serial for a group [message #639554 is a reply to message #639539] |
Thu, 09 July 2015 13:30 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Lalit Kumar B wrote on Thu, 09 July 2015 07:10Use COUNT()
And what has COUNT to do with this? Pure data magic:
with data(Facility, Store, Stop ) as(
select 620592, 'S0722', 1 from dual union all
select 620592, 'S0722', 2 from dual union all
select 620592, 'S0722', 3 from dual union all
select 620578, 'S0308', 4 from dual union all
select 620578, 'S0308', 5 from dual union all
select 620578, 'S0631', 6 from dual union all
select 620590, 'S0678', 7 from dual union all
select 620590, 'S0444', 8 from dual union all
select 620590, 'S0237', 9 from dual
)
select t.*, count(facility) over(partition by facility) rn
from data t
order by stop;
FACILITY STORE STOP RN
---------- ----- ---------- ----------
620592 S0722 1 3
620592 S0722 2 3
620592 S0722 3 3
620578 S0308 4 3
620578 S0308 5 3
620578 S0631 6 3
620590 S0678 7 3
620590 S0444 8 3
620590 S0237 9 3
9 rows selected.
SQL>
My understanding OP wants "Here Sequence should be based on group by Facility and order by Stop":
with data(Facility, Store, Stop ) as(
select 620592, 'S0722', 1 from dual union all
select 620592, 'S0722', 2 from dual union all
select 620592, 'S0722', 3 from dual union all
select 620578, 'S0308', 4 from dual union all
select 620578, 'S0308', 5 from dual union all
select 620578, 'S0631', 6 from dual union all
select 620590, 'S0678', 7 from dual union all
select 620590, 'S0444', 8 from dual union all
select 620590, 'S0237', 9 from dual
),
t as (
select d.*,
min(stop) over(partition by facility) min_stop
from data d
)
select facility,
store,
stop,
dense_rank() over(order by min_stop) sequence
from t
order by stop
/
FACILITY STORE STOP SEQUENCE
---------- ----- ---------- ----------
620592 S0722 1 1
620592 S0722 2 1
620592 S0722 3 1
620578 S0308 4 2
620578 S0308 5 2
620578 S0631 6 2
620590 S0678 7 3
620590 S0444 8 3
620590 S0237 9 3
9 rows selected.
SQL> -- and with original data
SQL> with data(Facility, Store, Stop ) as(
2 select 620592, 'S0722', 1 from dual union all
3 select 620578, 'S0308', 2 from dual union all
4 select 620578, 'S0631', 3 from dual union all
5 select 620590, 'S0678', 4 from dual union all
6 select 620590, 'S0444', 5 from dual union all
7 select 620590, 'S0237', 6 from dual
8 ),
9 t as (
10 select d.*,
11 min(stop) over(partition by facility) min_stop
12 from data d
13 )
14 select facility,
15 store,
16 stop,
17 dense_rank() over(order by min_stop) sequence
18 from t
19 order by stop
20 /
FACILITY STORE STOP SEQUENCE
---------- ----- ---------- ----------
620592 S0722 1 1
620578 S0308 2 2
620578 S0631 3 2
620590 S0678 4 3
620590 S0444 5 3
620590 S0237 6 3
6 rows selected.
SQL>
SY.
|
|
|
|
| Re: Same serial for a group [message #639555 is a reply to message #639554] |
Thu, 09 July 2015 14:05 |
|
Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Quote:And what has COUNT to do with this?
Just that "dense_rank() over(order by [min(stop) over(partition by facility)])" is [almost] logically equivalent to "count(distinct Facility) over (order by Facility)" (syntax that Oracle does not support but you can do the same thing with a simple dense_rank).
I don't know if the "order by Stop" means "order by min(stop)".
[Updated on: Thu, 09 July 2015 14:29] Report message to a moderator |
|
|
|
| Re: Same serial for a group [message #639556 is a reply to message #639555] |
Thu, 09 July 2015 14:16 |
|
vspn
Messages: 10
Registered: July 2015
|
Junior Member |
|
|
I didnt post the code as my query involves many other tables. Anyway I am posting here the query and the result..
SELECT DISTINCT
FACILITY_NOTE.FACILITY_ID,
STOP.FACILITY_ALIAS_ID AS STOP_DESTINATION,
Row_number() OVER ( PARTITION BY FACILITY_NOTE.FACILITY_ID ORDER BY STOP.STOP_SEQ ) AS SEQ,
ROUND(SUM(CASE
WHEN COALESCE(LPN.WEIGHT,0) = 0
THEN COALESCE(LPN.ESTIMATED_WEIGHT,0)
ELSE COALESCE(LPN.WEIGHT,0)
END)) AS WEIGHT,
STOP.DEPARTURE_START_DTTM AS DELV_DATE,
'1' AS MASTER_BOL
FROM WMS.SHIPMENT
INNER JOIN WMS.STOP
ON STOP.SHIPMENT_ID = SHIPMENT.SHIPMENT_ID
INNER JOIN WMS.FACILITY_NOTE
ON FACILITY_NOTE.NOTE = STOP.FACILITY_ALIAS_ID
AND FACILITY_NOTE.MARK_FOR_DELETION = 0
LEFT JOIN WMS.FACILITY_ALIAS
ON FACILITY_ALIAS.FACILITY_ID = STOP.FACILITY_ID
LEFT JOIN WMS.FACILITY
ON FACILITY.FACILITY_ID = STOP.FACILITY_ID
INNER JOIN WMS.STOP_ACTION
ON STOP_ACTION.STOP_SEQ = STOP.STOP_SEQ
AND STOP_ACTION.SHIPMENT_ID = STOP.SHIPMENT_ID
AND STOP_ACTION.ACTION_TYPE IN ('DL','DA')
INNER JOIN WMS.LPN
ON LPN.SHIPMENT_ID = SHIPMENT.SHIPMENT_ID
AND LPN.LPN_TYPE = 1
AND LPN.INBOUND_OUTBOUND_INDICATOR ='O'
AND LPN.LPN_FACILITY_STATUS <= 90
INNER JOIN WMS.ORDERS
ON ORDERS.ORDER_ID = LPN.ORDER_ID
AND ORDERS.IS_CANCELLED = 0
AND ORDERS.D_FACILITY_ID = STOP.FACILITY_ID
LEFT JOIN WMS.COMPANY_PARAMETER
ON COMPANY_PARAMETER.TC_COMPANY_ID = SHIPMENT.TC_COMPANY_ID
AND COMPANY_PARAMETER.PARAM_DEF_ID ='base_weight_uom'
LEFT JOIN WMS.SIZE_UOM
ON TRIM(CAST(SIZE_UOM.SIZE_UOM_ID AS CHAR(10))) = COMPANY_PARAMETER.PARAM_VALUE
AND SIZE_UOM.TC_COMPANY_ID = COMPANY_PARAMETER.TC_COMPANY_ID
LEFT JOIN
WMS.COMPANY_PARAMETER COMPANY_PARAM
ON COMPANY_PARAM.PARAM_DEF_ID = 'BOL_TYPE'
AND COMPANY_PARAM.PARAM_GROUP_ID = 'COM_TEPE'
AND COMPANY_PARAM.TC_COMPANY_ID = SHIPMENT.TC_COMPANY_ID
WHERE
'1986' = CASE TRIM(CAST('0' AS CHAR(1)))
WHEN '0' THEN TRIM(CAST(SHIPMENT.SHIPMENT_ID AS CHAR(20)))
WHEN '1' THEN TRIM(CAST(STOP.SHIPMENT_ID AS CHAR(20)))
END
AND
'0' = CASE TRIM(CAST('0' AS CHAR(1)))
WHEN '0' THEN '0'
WHEN '1' THEN TRIM(CAST(STOP.STOP_SEQ AS CHAR(10)))
END
AND '0' = '0'
GROUP BY
FACILITY_NOTE.FACILITY_ID,
STOP.FACILITY_ALIAS_ID,
STOP.STOP_SEQ,
STOP.DEPARTURE_START_DTTM
Order by FACILITY_NOTE.FACILITY_ID;
Output:
-----------------
Facility_id Stop_Destination Seq Weight Delv_Date Master_BOL
620592 S0722 1 920 08-MAY-15 1
620578 S0308 1 2443 12-MAY-15 1
620578 S0631 2 460 12-MAY-15 1
|
|
|
|
|
|
| Re: Same serial for a group [message #639558 is a reply to message #639555] |
Thu, 09 July 2015 15:36 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Michel Cadot wrote on Thu, 09 July 2015 15:05
Quote:And what has COUNT to do with this?
Just that "dense_rank() over(order by [min(stop) over(partition by facility)])" is [almost] logically equivalent to "count(distinct Facility) over (order by Facility)" (syntax that Oracle does not support but you can do the same thing with a simple dense_rank).
I don't know if the "order by Stop" means "order by min(stop)".
I see no equivalence. And my example shows that. Count simply returns how many rows have same facility value and if multiple facilities have same number of rows they will be assigned same serial number - obviously not what OP asked for. We could assign serial number solely based on facility dense rank, but it will not fulfill "order by Stop" part w3hic I understand as facility with stop 1 is assigned serial number 1, facility with lowest stop not taken by facility with serial number 1 is assigned serial number 2, etc. In sample provided all facilities have consecutive stop values, but OP never stated that's the rule. Compare results:
SQL> with data(Facility, Store, Stop ) as(
2 select 620592, 'S0722', 1 from dual union all
3 select 620578, 'S0308', 6 from dual union all
4 select 620578, 'S0631', 3 from dual union all
5 select 620590, 'S0678', 4 from dual union all
6 select 620590, 'S0444', 5 from dual union all
7 select 620590, 'S0237', 2 from dual
8 )
9 select t.*, count(facility) over(partition by facility) rn
10 from data t
11 order by rn
12 /
FACILITY STORE STOP RN
---------- ----- ---------- ----------
620592 S0722 1 1
620578 S0308 6 2
620578 S0631 3 2
620590 S0444 5 3
620590 S0678 4 3
620590 S0237 2 3
6 rows selected.
SQL> with data(Facility, Store, Stop ) as(
2 select 620592, 'S0722', 1 from dual union all
3 select 620578, 'S0308', 6 from dual union all
4 select 620578, 'S0631', 3 from dual union all
5 select 620590, 'S0678', 4 from dual union all
6 select 620590, 'S0444', 5 from dual union all
7 select 620590, 'S0237', 2 from dual
8 )
9 select facility,
10 store,
11 stop,
12 dense_rank() over(order by Facility) sequence
13 from data
14 order by sequence
15 /
FACILITY STORE STOP SEQUENCE
---------- ----- ---------- ----------
620578 S0631 3 1
620578 S0308 6 1
620590 S0237 2 2
620590 S0678 4 2
620590 S0444 5 2
620592 S0722 1 3
6 rows selected.
SQL>
SQL> with data(Facility, Store, Stop ) as(
2 select 620592, 'S0722', 1 from dual union all
3 select 620578, 'S0308', 6 from dual union all
4 select 620578, 'S0631', 3 from dual union all
5 select 620590, 'S0678', 4 from dual union all
6 select 620590, 'S0444', 5 from dual union all
7 select 620590, 'S0237', 2 from dual
8 )
9 ,
10 t as (
11 select d.*,
12 min(stop) over(partition by facility) min_stop
13 from data d
14 )
15 select facility,
16 store,
17 stop,
18 dense_rank() over(order by min_stop) sequence
19 from t
20 order by sequence
21 /
FACILITY STORE STOP SEQUENCE
---------- ----- ---------- ----------
620592 S0722 1 1
620590 S0237 2 2
620590 S0444 5 2
620590 S0678 4 2
620578 S0631 3 3
620578 S0308 6 3
6 rows selected.
SQL>
SY.
|
|
|
|
| Re: Same serial for a group [message #639563 is a reply to message #639558] |
Fri, 10 July 2015 00:33 |
|
Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Quote:obviously not what OP asked for
But what he asked is obviously not clear.
You focused on order, I focused on "group number" because order is not clear (what does mean "order by stop" when there are several stops in each group, min? max? avg? median? sum? product? sum of squares of differences with the min?...).
And if OP's latest result is correct, we are both wrong. 
[Updated on: Fri, 10 July 2015 00:42] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: Same serial for a group [message #639722 is a reply to message #639583] |
Tue, 14 July 2015 11:36 |
|
vspn
Messages: 10
Registered: July 2015
|
Junior Member |
|
|
In Solomon's queries, all the outputs shown are not as expected. Stops are always in sequence as given in my first post. So I need final serial or Sequence grouped by facility and ordered by min of Stop.
Let me tell the scenario for which I am writing the query.
We have many retail routes defined in the system, let's say it is Nashville. In this Nashville route, there are some stores/stops defined. Now the truck leaves from distribution center to deliver the goods to stores. Before even going to store, they go to crossdock location and from there on it goes to individual stores in the order defined in the Nashville route.
Say there are 5 stores/stops in the Nashville route. Each of these store belong to crossdock and multiple stores may have same crossdock.
So in the example shared there are 3 crossdock locations (620592, 620578, 620590) and 6 stores(S0722,S0308,S0631,S0678,S0444,S0237). Order they follow is fixed(i.e Stop is always fixed and always in sequence)
|
|
|
|
|
|
|
|
| Re: Same serial for a group [message #639728 is a reply to message #639727] |
Tue, 14 July 2015 14:39 |
|
vspn
Messages: 10
Registered: July 2015
|
Junior Member |
|
|
|
Also in Solomon's output, Facility 620578 cannot have stops as 6 and 3. They should always follow sequence like 2,3 or 3,4 or 4,5 or 5,6. Similarly for 620590, it has 5,4,2. But it should be like 4,5,6
|
|
|
|
|
|
| Re: Same serial for a group [message #639730 is a reply to message #639728] |
Tue, 14 July 2015 14:40 |
|
Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
vspn wrote on Tue, 14 July 2015 21:39Also in Solomon's output, Facility 620578 cannot have stops as 6 and 3. They should always follow sequence like 2,3 or 3,4 or 4,5 or 5,6. Similarly for 620590, it has 5,4,2. But it should be like 4,5,6
See my previous answer posted at the same than yours.
And I add, as several rows have the same SEQUENCE/RN, that is min(stop) you have to add another rule to order these rows.
[Updated on: Tue, 14 July 2015 14:42] Report message to a moderator |
|
|
|
|
|
|
|
Goto Forum:
Current Time: Wed May 08 01:29:57 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
