Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Rows in which the final quantity is equal to the initial_quantity-1

731844Nov 3 2009 — edited Nov 4 2009
Hi,

I have a table with the following columns: account_id, country_code, initial quantity, final quantity, unit.
For example:
account_id country_code initital quantity final quantity unit
555 FR 1 5 1
555 FR 6 8 1
555 FR 11 12 1

I want the rows in which the final quantity is equal to the initial_quantity-1.

For this I have the following query
select a.country_code,
a.account_id,
a.initial_quantity,
a.final_quantity,
a.unit,
from tableX a
inner join tableX b
on a.final_quantity+1=b.initial_quantity and a.unit=b.unit
where
and a.country_code=b.country_code
and a.account_id=b.account_id

Running the query, the result is
555 FR 1 5 1

But I want it to also display row:
555 FR 6 8 1

Because I eventually want to merge this in a single row
555 FR 1 8 1

Any ideas?

Thanks in advance.

Kind regards
This post has been answered by Solomon Yakobson on Nov 3 2009
Jump to Answer

Comments

Frank Kulash
Hi,

Welcome to the forum!

What if you have three (or more) rows chained together like that?
For example, if the data were: 
555 	FR	1	2	1
555	FR	3	5	1
555 	FR 	6 	8 	1

555 	FR 	11 	12 	1
Would you want this output? 
555 	FR	1	8	1
If so, a self-join won't work; you'll need analytic functions or CONNECT BY.

Another question, should the output include isolated rows like 
555 	FR 	11 	12 	1
731844
Hi,

Thank you very much for your reply.

I would want the result to be
555 FR 1 2 1
555 FR 3 5 1
555 FR 6 8 1

because eventually I want to merge this into:
555 FR 1 8 1

and the output should not include isolated rows.

I'm new to Oracle. What kind of analytic functions are you reffering to?

Thanks.

Kind regards
730185
Can you clarify your requirement, because given these two statements in your original post:
I have a table with the following columns: account_id, country_code, initial quantity, final quantity, unit.
I want the rows in which the final quantity is equal to the initial_quantity-1
Surely its just
SELECT * FROM tableX where final_quantity = initial_quantity - 1
731844
Hi,

Sorry my bad english. What I want is the rows in which the final quantity is equal to the initial_quantity-1 of a different row.

Thanks.

Kind regards
Frank Kulash
Hi,
user10943015 wrote:
Hi,

Thank you very much for your reply.

I would want the result to be
555 FR 1 2 1
555 FR 3 5 1
555 FR 6 8 1

because eventually I want to merge this into:
555 FR 1 8 1
I'm confused.
Do you want 3 rows of output or 1?

Assuming you want 1 row of output: 
WITH   got_grp_start	AS
(
	SELECT	account_id, country_code, initial_quantity, final_quantity, unit
	,	CASE
			WHEN  initial_quantity != 
			      LAG (final_quantity) OVER ( PARTITION BY  account_id
			      	  		   	  ,	    	country_code
							  ,		unit
							  ORDER BY	initial_quantity
							) + 1
			THEN  1
			ELSE  0
		END	AS grp_start
	FROM	tablex
--	WHERE	...		-- Any filtering goes here
)
,	got_grp_id	AS
(
	SELECT	account_id, country_code, initial_quantity, final_quantity, unit
	,	grp_start
	,	SUM (grp_start) OVER ( PARTITION BY  account_id
			      	       ,	     country_code
				       ,	     unit
				       ORDER BY      initial_quantity
				     ) 	     AS grp_id
	FROM	got_grp_start
)
SELECT	  account_id
,	  country_code
,	  MIN (initial_quantity)	AS initial_quantity
,	  MAX (final_quantity)		AS final_quantity
,	  unit
FROM	  got_grp_id
GROUP BY  account_id, country_code, unit, grp_id
HAVING	  COUNT (*)   > 1
ORDER BY  account_id, country_code, unit, grp_id
;
I tested this by creating and populating a table, like this: 
CREATE TABLE	tablex
(      account_id	NUMBER
,      country_code	VARCHAR2 (12)
,      initial_quantity	NUMBER
,      final_quantity	NUMBER
,      unit		NUMBER
);

INSERT INTO tablex (account_id, country_code, initial_quantity, final_quantity, unit) 
       VALUES 	   (555,	'FR',	      1,		5, 		1);
INSERT INTO tablex (account_id, country_code, initial_quantity, final_quantity, unit) 
       VALUES 	   (555,	'FR',	      6,		8, 		1);
INSERT INTO tablex (account_id, country_code, initial_quantity, final_quantity, unit) 
       VALUES 	   (555,	'FR',	      11,		12, 		1);
INSERT INTO tablex (account_id, country_code, initial_quantity, final_quantity, unit) 
       VALUES 	   (555,	'DE',	      1,		2,  		1);
INSERT INTO tablex (account_id, country_code, initial_quantity, final_quantity, unit) 
       VALUES 	   (555,	'DE',	      3,		5,  		1);
INSERT INTO tablex (account_id, country_code, initial_quantity, final_quantity, unit) 
       VALUES 	   (555,	'DE',	      6,		8,  		1);
INSERT INTO tablex (account_id, country_code, initial_quantity, final_quantity, unit) 
       VALUES 	   (555,	'DE',	      11,		12, 		1);
COMMIT;
Whenever you post a question on this forum, you should include CREATE TABLE and INSERT statements like this. It helps clarify what you mean (and that helps a lot when talking to people who speak a different language), and it also allows people to test their ideas.

Notice that the data above has two different country codes.
'FR' is exactly what you posted, with 2 rows forming a group.
'DE' is what I asked about in my last message, with 3 rows in a group.

The output from this data is: 
.  ACCOUNT COUNTRY     INITAL      FINAL
       _ID _CODE    _QUANTITY  _QUANTITY       UNIT
---------- ------- ---------- ---------- ----------
       555 DE               1          8          1
       555 FR               1          8          1
If you really want the 3 rows of input, add a column called grp_end that is just like grp_start, except that it uses the LEAD function instead of LAG.
Then query that result set, choosing only rows <tt>WHERE grp_start = 0 OR grp_end = 0</tt>

Edited by: Frank Kulash on Nov 3, 2009 12:48 PM
3-row solution actually requires a little adjustment. See my bnext message, below.
1 person found this helpful
731844
Hi,

Again thank you very much for your help and for your tips. I will try to use them in the future.

In my example, I want 3 rows of output.
555 FR 1 2 1
555 FR 3 5 1
555 FR 6 8 1


In your example, the result I want is:
account_id country initial_quantity final_quantity unit
555 FR 1 5 1
555 FR 6 8 1
555 DE 1 2 1
555 DE 3 5 1
555 DE 6 8 1

Thanks

Kind regards,
Joana Simões
Frank Kulash
Hi, Joana,
user10943015 wrote:
Hi,

Again thank you very much for your help and for your tips. I will try to use them in the future.

In my example, I want 3 rows of output.
555 FR 1 2 1
555 FR 3 5 1
555 FR 6 8 1
The basic plan at the end of my last message is still valid, but the details aren't quite as easy as I described.
What you requested is: 
WITH   got_grp_start	AS
(
	SELECT	account_id, country_code, initial_quantity, final_quantity, unit
	,	CASE
			WHEN  initial_quantity = 
			      LAG (final_quantity) OVER ( PARTITION BY  account_id
			      	  		   	  ,	    	country_code
							  ,		unit
							  ORDER BY	initial_quantity
							) + 1
			THEN  0
			ELSE  1
		END	AS grp_start
	,	CASE
			WHEN  final_quantity = 
			      LEAD (initial_quantity) OVER ( PARTITION BY  account_id
			      	  		   	     ,	    	   country_code
							     ,		   unit
							     ORDER BY	   initial_quantity
							   ) - 1
			THEN  0
			ELSE  1
		END	AS grp_end
	FROM	tablex
--	WHERE	...		-- Any filtering goes here
)
SELECT	account_id, country_code, initial_quantity, final_quantity, unit
FROM	got_grp_start
WHERE	grp_start	= 0
OR	grp_end		= 0
;
Output from my earlier sample data: 
.  ACCOUNT COUNTRY     INITAL      FINAL
       _ID _CODE    _QUANTITY  _QUANTITY       UNIT
---------- ------- ---------- ---------- ----------
       555 DE               1          2          1
       555 DE               3          5          1
       555 DE               6          8          1
       555 FR               1          5          1
       555 FR               6          8          1
You could also get these results using an EXISTS sub-query.
1 person found this helpful
Solomon Yakobson
Answer
Hi Frank,

I am not sure you can rely on:
ORDER BY initial_quantity
Initial quantity could be not necessarily increasing or decreasing. It can be both:
initial final
------- -----
1       5
3       4
6       2
11     12
Then LAG/LEAD will not work:
INSERT INTO tablex (account_id, country_code, initial_quantity, final_quantity, unit) 
       VALUES 	   (555,	'FR',	      1,		5, 		1);
INSERT INTO tablex (account_id, country_code, initial_quantity, final_quantity, unit) 
       VALUES 	   (555,	'FR',	      6,		2, 		1);
INSERT INTO tablex (account_id, country_code, initial_quantity, final_quantity, unit) 
       VALUES 	   (555,	'FR',	      3,		4, 		1);
INSERT INTO tablex (account_id, country_code, initial_quantity, final_quantity, unit) 
       VALUES 	   (555,	'FR',	      11,		12, 		1);
INSERT INTO tablex (account_id, country_code, initial_quantity, final_quantity, unit) 
       VALUES 	   (555,	'DE',	      1,		2,  		1);
INSERT INTO tablex (account_id, country_code, initial_quantity, final_quantity, unit) 
       VALUES 	   (555,	'DE',	      3,		5,  		1);
INSERT INTO tablex (account_id, country_code, initial_quantity, final_quantity, unit) 
       VALUES 	   (555,	'DE',	      6,		8,  		1);
INSERT INTO tablex (account_id, country_code, initial_quantity, final_quantity, unit) 
       VALUES 	   (555,	'DE',	      11,		12, 		1);
COMMIT;

SQL> select * from tablex
  2  /

ACCOUNT_ID COUNTRY_CODE INITIAL_QUANTITY FINAL_QUANTITY       UNIT
---------- ------------ ---------------- -------------- ----------
       555 FR                          1              5          1
       555 FR                          6              2          1
       555 FR                          3              4          1
       555 FR                         11             12          1
       555 DE                          1              2          1
       555 DE                          3              5          1
       555 DE                          6              8          1
       555 DE                         11             12          1

8 rows selected.

SQL> WITH   got_grp_start AS
  2  (
  3   SELECT account_id, country_code, initial_quantity, final_quantity, unit
  4   , CASE
  5     WHEN  initial_quantity = 
  6           LAG (final_quantity) OVER ( PARTITION BY  account_id
  7                      ,      country_code
  8           ,  unit
  9           ORDER BY initial_quantity
 10         ) + 1
 11     THEN  0
 12     ELSE  1
 13    END AS grp_start
 14   , CASE
 15     WHEN  final_quantity = 
 16           LEAD (initial_quantity) OVER ( PARTITION BY  account_id
 17                         ,         country_code
 18              ,     unit
 19              ORDER BY    initial_quantity
 20            ) - 1
 21     THEN  0
 22     ELSE  1
 23    END AS grp_end
 24   FROM tablex
 25  -- WHERE ...  -- Any filtering goes here
 26  )
 27  SELECT account_id, country_code, initial_quantity, final_quantity, unit
 28  FROM got_grp_start
 29  WHERE grp_start = 0
 30  OR grp_end  = 0
 31  ;

ACCOUNT_ID COUNTRY_CODE INITIAL_QUANTITY FINAL_QUANTITY       UNIT
---------- ------------ ---------------- -------------- ----------
       555 DE                          1              2          1
       555 DE                          3              5          1
       555 DE                          6              8          1

SQL>
I think hierarchical query might be a better approach:
SELECT  DISTINCT *
  FROM  tablex
  START WITH ROWID IN (
                       SELECT  a.ROWID
                         FROM  tablex a,
                               tablex b
                         WHERE b.account_id = a.account_id
                           AND b.country_code = a.country_code
                           AND b.unit = a.unit
                           AND b.initial_quantity = a.final_quantity + 1
                      )
  CONNECT BY NOCYCLE account_id = PRIOR account_id
                 AND country_code = PRIOR country_code
                 AND unit = PRIOR unit
                 AND initial_quantity = PRIOR final_quantity + 1
  ORDER BY account_id,
           country_code,
           unit,
           initial_quantity
/

ACCOUNT_ID COUNTRY_CODE INITIAL_QUANTITY FINAL_QUANTITY       UNIT
---------- ------------ ---------------- -------------- ----------
       555 DE                          1              2          1
       555 DE                          3              5          1
       555 DE                          6              8          1
       555 FR                          1              5          1
       555 FR                          3              4          1
       555 FR                          6              2          1

6 rows selected.

SQL>
SY.
Marked as Answer by 731844 · Sep 27 2020
Frank Kulash
Hi,
Solomon Yakobson wrote:
Hi Frank,

I am not sure you can rely on:
ORDER BY initial_quantity
Initial quantity could be not necessarily increasing or decreasing. It can be both:
initial final
------- -----
1       5
3       4
6       2
11     12
Good point!

Among the many things I assumed was that the initial_quantity-to-final_quantity range for any row did not overlap any other row with the same account_id, country_code and unit.

I think EXISTS is the best thing for this problem: 
SELECT	*
FROM	tablex	m
WHERE	EXISTS	( SELECT  NULL
		  FROM	  tablex
		  WHERE	  account_id	= m.account_id
		  AND	  country_code	= m.country_code
		  AND	  unit		= m.unit
		  AND	  (	initial_quantity	= m.final_quantity   + 1
		  	  OR	final_quantity		= m.initial_quantity - 1
			  )
		)
;
The query above will work with overlapping ranges.
1 person found this helpful
731844
Hi Frank and Solomon,

Both your queries are correct and answered my question.

Thank you very much.

Kind regards

Edited by: JoanaSimoes on 4/Nov/2009 1:35
1 - 10
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Dec 2 2009
Added on Nov 3 2009
10 comments
1,005 views