OraFAQ Forum: SQL & PL/SQL » Min and Max values of contiguous rows (3 merged)

Home » SQL & PL/SQL » SQL & PL/SQL » Min and Max values of contiguous rows (3 merged) (oracle 10g)

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

Min and Max values of contiguous rows (3 merged) [message #686177]

Wed, 29 June 2022 11:31

arun888
Messages: 99
Registered: June 2015
Location: INDIA

Member

not sure how i can take the min and max values of the sequence of rows from the below input file.

[Input]

code	market	week
sd	1067	1994
sd	1067	1995
sd	1067	2005
sd	1067	2027
sd	1067	2028
sd	1067	2029
sd	1067	2030
sd	1067	2041
sd	1067	2042
sd	1067	2043
sd	1067	2104
sd	1067	2121
sd	1067	2157
sd	1067	2163
sd	1067	2164
sd	1067	2165
sd	1067	2166
sd	1067	2167
sd	1067	2188
sd	1067	2206
sd	1067	2207
sd	1067	2210
sd	1067	2213
sd	1067	2214
sd	1067	2216
sd	1067	2218
sd	1067	2219
sd	1067	2220
sd	1067	2221
sd	1067	2222
sd	1067	2223
sd	1067	2224
sd	1067	2225
sd	1067	2226
sd	1067	2227

Expected output to find the min and max of the contiqous rows.

code	market	Min(Week)	Max(Week)
sd	1067	1994	1995
sd	1067	2005	2005
sd	1067	2027	2030
sd	1067	2041	2043
sd	1067	2104	2104
sd	1067	2121	2121
sd	1067	2157	2157
sd	1067	2163	2167
sd	1067	2188	2188
sd	1067	2206	2207
sd	1067	2210	2210
sd	1067	2213	2214
sd	1067	2216	2216
sd	1067	2218	2227

Report message to a moderator

Re: Min and Max values of contiguous rows (3 merged) [message #686182 is a reply to message #686177]

Wed, 29 June 2022 11:56

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

Senior Member
Account Moderator

With any SQL or PL/SQL question, please, post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

This has been asked many times, see this topic for instance.

Report message to a moderator

Re: Min and Max values of contiguous rows (3 merged) [message #686196 is a reply to message #686177]

Thu, 30 June 2022 00:57

arun888
Messages: 99
Registered: June 2015
Location: INDIA

Member

Thanks. Works as expected,
Can you let know to get work for all market instead of 1067.

with t as (
            select  market_no,
                    trunc(week) week,
                    trunc(week) - row_number() over(order by trunc(week)) grp
              from  imputed_markets 
           )
 select  market_no,
         min(week) week_start,
         max(week) week_end
   from  t
   group by market_no,
            grp
   order by market_no,
            grp
            
           
Current Output :
1067	2233	2233
1067	2232	2232
1067	2231	2231
1067	2230	2230
1067	2229	2229
1067	2228	2228
1067	2227	2227
1067	2226	2226
1067	2225	2225
1067	2224	2224
1067	2223	2223
1067	2222	2222
1067	2221	2221
1067	2220	2220
1067	2219	2219
1067	2218	2218
1067	2216	2216
1067	2214	2214
1067	2213	2213
1067	2210	2210
1067	2207	2207
1067	2206	2206
1067	2188	2188
1067	2167	2167
1067	2166	2166
1067	2165	2165
1067	2164	2164
1067	2163	2163
1067	2157	2157
1067	2121	2121
1067	2104	2104
1067	2043	2043
1067	2042	2042
1067	2041	2041
1067	2030	2030
1067	2029	2029
1067	2028	2028
1067	2027	2027
1067	2005	2005
1067	1995	1995
1067	1994	1994

Changed query with where market_no=1067

Query :

  with t as (
              select  store_no,
                      trunc(week) week,
                      trunc(week) - row_number() over(order by trunc(week)) grp
                from  imputed_stores where store_no=1067
             )
   select  store_no,
           min(week) week_start,
           max(week) week_end
     from  t
     group by store_no,
              grp
     order by store_no,
              grp
			  
			  
Output Received as expected :

1067	1994	1995
1067	2005	2005
1067	2027	2030
1067	2041	2043
1067	2104	2104
1067	2121	2121
1067	2157	2157
1067	2163	2167
1067	2188	2188
1067	2206	2207
1067	2210	2210
1067	2213	2214
1067	2216	2216
1067	2218	2233

Can you let me know to get the expected output for all market number instead of 1067.

[Updated on: Thu, 30 June 2022 00:58]

Report message to a moderator

Re: Min and Max values of contiguous rows (3 merged) [message #686197 is a reply to message #686196]

Thu, 30 June 2022 01:12

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

Senior Member
Account Moderator

Michel Cadot wrote on Wed, 29 June 2022 18:56

With any SQL or PL/SQL question, please, post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
...

Report message to a moderator

Re: Min and Max values of contiguous rows (3 merged) [message #686198 is a reply to message #686197]

Thu, 30 June 2022 01:34

arun888
Messages: 99
Registered: June 2015
Location: INDIA

Member

Created a testcase.

create table testcase1(market_no number, week   number);
insert into testcase1 (market_no, week) values (1067, 1994);
insert into testcase1 (market_no, week) values (1067, 1995);
insert into testcase1 (market_no, week) values (1067, 2005);
insert into testcase1 (market_no, week) values (1067, 2027);
insert into testcase1 (market_no, week) values (1067, 2028);
insert into testcase1 (market_no, week) values (1067, 2029);
insert into testcase1 (market_no, week) values (1067, 2030);
insert into testcase1 (market_no, week) values (1067, 2041);
insert into testcase1 (market_no, week) values (1067, 2042);
insert into testcase1 (market_no, week) values (1067, 2043);
insert into testcase1 (market_no, week) values (1067, 2104);
insert into testcase1 (market_no, week) values (1067, 2121);
insert into testcase1 (market_no, week) values (1067, 2157);
insert into testcase1 (market_no, week) values (1067, 2163);
insert into testcase1 (market_no, week) values (1067, 2164);
insert into testcase1 (market_no, week) values (1067, 2165);
insert into testcase1 (market_no, week) values (1067, 2166);
insert into testcase1 (market_no, week) values (1067, 2167);
insert into testcase1 (market_no, week) values (1067, 2188);
insert into testcase1 (market_no, week) values (1067, 2206);
insert into testcase1 (market_no, week) values (1067, 2207);
insert into testcase1 (market_no, week) values (1067, 2210);
insert into testcase1 (market_no, week) values (1067, 2213);
insert into testcase1 (market_no, week) values (1067, 2214);
insert into testcase1 (market_no, week) values (1067, 2216);
insert into testcase1 (market_no, week) values (1067, 2218);
insert into testcase1 (market_no, week) values (1067, 2219);
insert into testcase1 (market_no, week) values (1067, 2220);
insert into testcase1 (market_no, week) values (1067, 2221);
insert into testcase1 (market_no, week) values (1067, 2222);
insert into testcase1 (market_no, week) values (1067, 2223);
insert into testcase1 (market_no, week) values (1067, 2224);
insert into testcase1 (market_no, week) values (1067, 2225);
insert into testcase1 (market_no, week) values (1067, 2226);
insert into testcase1 (market_no, week) values (1067, 2227);
insert into testcase1 (market_no, week) values (2028, 1994);
insert into testcase1 (market_no, week) values (2028, 1995);
insert into testcase1 (market_no, week) values (2028, 2005);
insert into testcase1 (market_no, week) values (2028, 2027);
insert into testcase1 (market_no, week) values (2028, 2028);
insert into testcase1 (market_no, week) values (2028, 2029);
insert into testcase1 (market_no, week) values (2028, 2030);
insert into testcase1 (market_no, week) values (2028, 2041);
insert into testcase1 (market_no, week) values (2028, 2042);
insert into testcase1 (market_no, week) values (2028, 2043);
insert into testcase1 (market_no, week) values (2028, 2104);
insert into testcase1 (market_no, week) values (2028, 2121);
insert into testcase1 (market_no, week) values (2028, 2157);
insert into testcase1 (market_no, week) values (2028, 2163);
insert into testcase1 (market_no, week) values (2028, 2164);
insert into testcase1 (market_no, week) values (2028, 2165);
insert into testcase1 (market_no, week) values (2028, 2166);
insert into testcase1 (market_no, week) values (2028, 2167);
insert into testcase1 (market_no, week) values (2028, 2188);
insert into testcase1 (market_no, week) values (2028, 2206);
insert into testcase1 (market_no, week) values (2028, 2207);
insert into testcase1 (market_no, week) values (2028, 2210);
insert into testcase1 (market_no, week) values (2028, 2213);
insert into testcase1 (market_no, week) values (2028, 2214);
insert into testcase1 (market_no, week) values (2028, 2216);
insert into testcase1 (market_no, week) values (2028, 2218);
insert into testcase1 (market_no, week) values (2028, 2219);
insert into testcase1 (market_no, week) values (2028, 2220);
insert into testcase1 (market_no, week) values (2028, 2221);
insert into testcase1 (market_no, week) values (2028, 2222);
insert into testcase1 (market_no, week) values (2028, 2223);
insert into testcase1 (market_no, week) values (2028, 2224);
insert into testcase1 (market_no, week) values (2028, 2225);
insert into testcase1 (market_no, week) values (2028, 2226);
insert into testcase1 (market_no, week) values (2028, 2227);

Currently, i am getting desired out for 1067 market values. how do i get the desired output for all market values. example for 2028 and 1067 and etc.

Current query and output :

  with t as (
              select  market_no,
                      trunc(week) week,
                      trunc(week) - row_number() over(order by trunc(week)) grp
                from  testcase1 where market_no=1067
             )
   select  market_no,
           min(week) week_start,
           max(week) week_end
     from  t
     group by market_no,
              grp
     order by market_no,
              grp
              
Ouptut:
market_no week_Start week_end
1067	1994	1995
1067	2005	2005
1067	2027	2030
1067	2041	2043
1067	2104	2104
1067	2121	2121
1067	2157	2157
1067	2163	2167
1067	2188	2188
1067	2206	2207
1067	2210	2210
1067	2213	2214
1067	2216	2216
1067	2218	2227

Expected output for all market values,


market_no week_Start week_end
1067	1994	1995
1067	2005	2005
1067	2027	2030
1067	2041	2043
1067	2104	2104
1067	2121	2121
1067	2157	2157
1067	2163	2167
1067	2188	2188
1067	2206	2207
1067	2210	2210
1067	2213	2214
1067	2216	2216
1067	2218	2227
2028	1994	1995
2028	2005	2005
2028	2027	2030
2028	2041	2043
2028	2104	2104
2028	2121	2121
2028	2157	2157
2028	2163	2167
2028	2188	2188
2028	2206	2207
2028	2210	2210
2028	2213	2214
2028	2216	2216
2028	2218	2227

[Updated on: Thu, 30 June 2022 02:03]

Report message to a moderator

Re: Min and Max values of contiguous rows (3 merged) [message #686199 is a reply to message #686198]

Thu, 30 June 2022 02:33

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

Senior Member
Account Moderator

Add the market_no in the ORDER BY clause of ROW_NUMBER and remove the WHERE clause.
Note: TRUNC is useless in your case.

SQL> with t as (
  2                select  market_no,
  3                        week,
  4                        week - row_number() over(order by market_no,week) grp
  5                  from  testcase1 -- where market_no=1067
  6               )
  7     select  market_no,
  8             min(week) week_start,
  9             max(week) week_end
 10       from  t
 11       group by market_no, grp
 12       order by market_no, grp
 13  /
 MARKET_NO WEEK_START   WEEK_END
---------- ---------- ----------
      1067       1994       1995
      1067       2005       2005
      1067       2027       2030
      1067       2041       2043
      1067       2104       2104
      1067       2121       2121
      1067       2157       2157
      1067       2163       2167
      1067       2188       2188
      1067       2206       2207
      1067       2210       2210
      1067       2213       2214
      1067       2216       2216
      1067       2218       2227
      2028       1994       1995
      2028       2005       2005
      2028       2027       2030
      2028       2041       2043
      2028       2104       2104
      2028       2121       2121
      2028       2157       2157
      2028       2163       2167
      2028       2188       2188
      2028       2206       2207
      2028       2210       2210
      2028       2213       2214
      2028       2216       2216
      2028       2218       2227

Report message to a moderator

Re: Min and Max values of contiguous rows (3 merged) [message #686210 is a reply to message #686199]

Fri, 01 July 2022 07:12

arun888
Messages: 99
Registered: June 2015
Location: INDIA

Member

Query works as expected.

Report message to a moderator

Re: Min and Max values of contiguous rows (3 merged) [message #686472 is a reply to message #686197]

Thu, 22 September 2022 02:01

arun888
Messages: 99
Registered: June 2015
Location: INDIA

Member

Not sure how i can the below query without creating the temp table.

SPOOL $BIN/$FILE
drop table tst;
create table tst as with t as ( select ret_code, country_no, trunc(week) week, trunc(week) - row_number() over(order by ret_code, country_no, week) grp from imputed_country) select ret_code, country_no, min(day) day_start, max(day) day_end from t group by ret_code, country_no, grp order by ret_code, country_no, grp;
select RET_CODE, COUNTRY_NO, DAY_START, DAY_END, ((day_end-day_start)+1) as "Number of days last polling" from TST where day_Start<=$day and day_end =$day and RET_CODE=(select ret_code from ret where ret_name='$chain');
SPOOL OFF
EXIT
EOF

Report message to a moderator

Re: Min and Max values of contiguous rows (3 merged) [message #686473 is a reply to message #686472]

Thu, 22 September 2022 02:10

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

Senior Member
Account Moderator

Just:

with tst as ( select ret_code, country_no, trunc(week) week, trunc(week) - row_number() over(order by ret_code, country_no, week) grp from imputed_country) select ret_code, country_no, min(day) day_start, max(day) day_end from t group by ret_code, country_no, grp order by ret_code, country_no, grp )
select RET_CODE, COUNTRY_NO, DAY_START, DAY_END, ((day_end-day_start)+1) as "Number of days last polling" from TST where day_Start<=$day and day_end =$day and RET_CODE=(select ret_code from ret where ret_name='$chain');

Report message to a moderator

Re: Min and Max values of contiguous rows (3 merged) [message #686474 is a reply to message #686473]

Thu, 22 September 2022 04:17

arun888
Messages: 99
Registered: June 2015
Location: INDIA

Member

with tst as ( select ret_code, country_no, trunc(week) week, trunc(week) - row_number() over(order by ret_code, country_no, week) grp from imputed_country) select ret_code, country_no, min(day) day_start, max(day) day_end from t group by ret_code, country_no, grp order by ret_code, country_no, grp )select RET_CODE, COUNTRY_NO, DAY_START, DAY_END, ((day_end-day_start)+1) as "Number of days last polling" from TST where day_Start<=$day and day_end =$day and RET_CODE=(select ret_code from ret where ret_name='$chain');

Getting an error while running the above query. not sure bracket is missing.

I would need to process based upon the ret_code.

[Updated on: Thu, 22 September 2022 04:25]

Report message to a moderator

Re: Min and Max values of contiguous rows (3 merged) [message #686476 is a reply to message #686474]

Thu, 22 September 2022 04:34

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

Senior Member
Account Moderator

with t as ( select ret_code, country_no, trunc(week) week, trunc(week) - row_number() over(order by ret_code, country_no, week) grp from imputed_country), tst as ( select ret_code, country_no, min(day) day_start, max(day) day_end from t group by ret_code, country_no, grp order by ret_code, country_no, grp )
select RET_CODE, COUNTRY_NO, DAY_START, DAY_END, ((day_end-day_start)+1) as "Number of days last polling" from TST where day_Start<=$day and day_end =$day and RET_CODE=(select ret_code from ret where ret_name='$chain');

Report message to a moderator

Re: Min and Max values of contiguous rows (3 merged) [message #687516 is a reply to message #686177]

Fri, 24 March 2023 01:46

arun888
Messages: 99
Registered: June 2015
Location: INDIA

Member

not sure how to add the mapping_no column from the country table to the below query.

Current Query :

WITH t
     AS (SELECT ret_code,
                country_no,
                day,
                day - Row_number()
                        OVER (
                          ORDER BY ret_code, country_no, day) grp
         FROM   ret.imputed_country
         WHERE  ret_code = (SELECT ret_code
                            FROM   ret.ret
                            WHERE  ret_name = 'd2')),
     tst
     AS (SELECT ret_code,
                country_no,
                Min(day) day_start,
                Max(day) day_end
         FROM   t
         GROUP  BY ret_code,
                   country_no,
                   grp)
SELECT ret_code,
       b.ron_census_country_no         AS "RON country",
       country_no,
       day_end                         AS "Report day",
       ( ( day_end - day_start ) + 1 ) AS "Count of Copies"
FROM   tst a
       JOIN usd_euroscan b
         ON a.country_no = b.retailer_country_no
WHERE  day_start <= 2272
       AND day_end = 2272
       AND b.ret_file_abbrev = 'd2'
ORDER  BY country_no,
          ret_code;

Current Query Output :

RET_CODE,country_no            ,day_START,  day_END,No of times since last polled
---,--------------------,----------,----------,-----------------------------
d2,106                 ,      2270,      2272,                            3
d2,1075                ,      2247,      2272,                           26
d2,1093                ,      2246,      2272,                           27
d2,1096                ,      2253,      2272,                           20
d2,1105                ,      2271,      2272,                            2

Mapping no table.


select country_no, mapping_no from country.

country_no mapping_no
106        1000
1075       2000
1093       3000
1105       4000

Expected Output :

RET_CODE,country_no,mapping_no            ,day_START,  day_END,No of days since last polled
---,-----------,---------,----------,----------,-----------------------------
d2,106,1000              ,      2270,      2272,                            3
d2,1075,2000             ,      2247,      2272,                           26
d2,1093,3000             ,      2246,      2272,                           27
d2,1096,4000             ,      2253,      2272,                           20

[Updated on: Fri, 24 March 2023 09:32]

Report message to a moderator

Previous Topic:	xml converting task
Next Topic:	sql

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Oct 04 19:31:19 CDT 2023