Home » SQL & PL/SQL » SQL & PL/SQL » A Query to Display Sequence of Numbers in a Order (Oracle 10g, Win Xp)
A Query to Display Sequence of Numbers in a Order [message #613679] |
Fri, 09 May 2014 02:19 |
|
stalin4d
Messages: 226 Registered: May 2010 Location: Chennai, Tamil Nadu, Indi...
|
Senior Member |
|
|
Hi,
I have Some srlno which is mentioned below
7736
7737
7738
7739
7740
7750
7761
7765
7779
7856
8047
8056
8058
8520
8521
8522
8523
This I need to print like this below;
7736 to 7740,7750,7761,7765,7779,7856,8047,8056,8058,8520 to 8523
it means whenever continuos srlno comes that should be print in (from to To format),
other wise that should be in single.
Please help me,and give solution with query.
|
|
|
Re: A Query to Display Sequence of Numbers in a Order [message #613690 is a reply to message #613679] |
Fri, 09 May 2014 03:10 |
|
Michel Cadot
Messages: 68624 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
There are several ways to do it, here's one:
with
data as (
select srlno,
decode(lag(srlno,1,0) over (order by srlno),
srlno-1, null,
row_number() over (order by srlno)) flag
from your_table
),
grouped as (
select srlno,
last_value(flag ignore nulls) over (order by srlno) grp
from data
)
select min(srlno)||decode(max(srlno),min(srlno),null,' to '||max(srlno)) range
from grouped
group by grp
order by grp
/
Of course not tested as you still refuse to post a test case.
|
|
|
Re: A Query to Display Sequence of Numbers in a Order [message #613692 is a reply to message #613690] |
Fri, 09 May 2014 03:20 |
|
Michel Cadot
Messages: 68624 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Or better:
SQL> with
2 source as (
3 select 7736 srlno from dual union all
4 select 7737 from dual union all
5 select 7738 from dual union all
6 select 7739 from dual union all
7 select 7740 from dual union all
8 select 7750 from dual union all
9 select 7761 from dual union all
10 select 7765 from dual union all
11 select 7779 from dual union all
12 select 7856 from dual union all
13 select 8047 from dual union all
14 select 8056 from dual union all
15 select 8058 from dual union all
16 select 8520 from dual union all
17 select 8521 from dual union all
18 select 8522 from dual union all
19 select 8523 from dual
20 ),
21 data as (
22 select srlno,
23 decode(lag(srlno,1,0) over (order by srlno),
24 srlno-1, to_number(null),
25 row_number() over (order by srlno)) flag
26 from source
27 ),
28 grouped as (
29 select srlno,
30 last_value(flag ignore nulls) over (order by srlno) grp
31 from data
32 )
33 select min(srlno)||decode(max(srlno),min(srlno),null,' to '||max(srlno)) range
34 from grouped
35 group by grp
36 order by grp
37 /
RANGE
------------------------------------------------------------------------------------
7736 to 7740
7750
7761
7765
7779
7856
8047
8056
8058
8520 to 8523
|
|
|
|
Re: A Query to Display Sequence of Numbers in a Order [message #613695 is a reply to message #613693] |
Fri, 09 May 2014 04:46 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
stalin4d wrote on Fri, 09 May 2014 13:54any other ideas pls...
Prior to 11g there are other string aggregation techniques. If you are in 11g, you can play around with LISTAGG :
SQL> WITH DATA AS(
2 SELECT 7736 val FROM dual UNION ALL
3 SELECT 7737 val FROM dual UNION ALL
4 SELECT 7738 val FROM dual UNION ALL
5 SELECT 7739 val FROM dual UNION ALL
6 SELECT 7740 val FROM dual UNION ALL
7 SELECT 7750 val FROM dual UNION ALL
8 SELECT 7761 val FROM dual UNION ALL
9 SELECT 7765 val FROM dual UNION ALL
10 SELECT 7779 val FROM dual UNION ALL
11 SELECT 7856 val FROM dual UNION ALL
12 SELECT 8047 val FROM dual UNION ALL
13 SELECT 8056 val FROM dual UNION ALL
14 SELECT 8058 val FROM dual UNION ALL
15 SELECT 8520 val FROM dual UNION ALL
16 SELECT 8521 val FROM dual UNION ALL
17 SELECT 8522 val FROM dual UNION ALL
18 SELECT 8523 val FROM dual)
19 SELECT 'FROM ' || MIN_VAL || ' to ' ||
20 SUBSTR(NUM_LIST,
21 INSTR(NUM_LIST, ',', 1) + 1,
22 INSTR(NUM_LIST, ',', -1, 2) - 1) || ' to ' || MAX_VAL
23 FROM (SELECT MIN(VAL) MIN_VAL,
24 LISTAGG(VAL, ',') WITHIN GROUP(ORDER BY VAL) NUM_LIST,
25 MAX(VAL) MAX_VAL
26 FROM DATA)
27 /
'FROM'||MIN_VAL||'TO'||SUBSTR(
-----------------------------------------------------------------------------------------------
FROM 7736 to 7737,7738,7739,7740,7750,7761,7765,7779,7856,8047,8056,8058,8520,8521,8522 to 8523
I guess someone good with REGULAR EXPRESSIONS can make it look it more tidy.
[Updated on: Fri, 09 May 2014 04:57] Report message to a moderator
|
|
|
|
|
Re: A Query to Display Sequence of Numbers in a Order [message #613699 is a reply to message #613692] |
Fri, 09 May 2014 05:50 |
Solomon Yakobson
Messages: 3269 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Michel Cadot wrote on Fri, 09 May 2014 04:20Or better:
No need for for lag and last_value:
with source as (
select 7736 srlno from dual union all
select 7737 from dual union all
select 7738 from dual union all
select 7739 from dual union all
select 7740 from dual union all
select 7750 from dual union all
select 7761 from dual union all
select 7765 from dual union all
select 7779 from dual union all
select 7856 from dual union all
select 8047 from dual union all
select 8056 from dual union all
select 8058 from dual union all
select 8520 from dual union all
select 8521 from dual union all
select 8522 from dual union all
select 8523 from dual
),
t as (
select srlno - row_number() over(order by srlno) grp,
srlno
from source
)
select case max(srlno)
when min(srlno) then to_char(max(srlno))
else min(srlno) || ' to ' || max(srlno)
end range
from t
group by grp
order by grp
/
RANGE
------------------
7736 to 7740
7750
7761
7765
7779
7856
8047
8056
RANGE
------------------
8058
8520 to 8523
10 rows selected.
SQL>
SY.
|
|
|
Goto Forum:
Current Time: Thu Mar 28 10:31:33 CDT 2024
|