Discussions
Categories
- 196.9K All Categories
- 2.2K Data
- 239 Big Data Appliance
- 1.9K Data Science
- 450.4K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 546 SQLcl
- 4K SQL Developer Data Modeler
- 187.1K SQL & PL/SQL
- 21.3K SQL Developer
- 295.9K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.6K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 443 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
analytical function help

I have following table:
with tbl as (select 1 col1, 'a' col2 from dual union select 2 , 'a' from dual union select 3 , 'b' from dual union select 4 , 'a' from dual union select 5 , 'a' from dual union select 6 , 'b' from dual union select 7 , 'b' from dual) select * from tbl order by col1;
I want following result i.e first value from col2 whenever col2 value changes.
1 ,a
3 ,b
4 ,a
6 ,b
How can I do this in SQL using analytical function
Best Answer
-
Here is one way (others my have better offerings):
with tbl as (select 1 col1, 'a' col2 from dual
union select 2 , 'a' from dual
union select 3 , 'b' from dual
union select 4 , 'a' from dual
union select 5 , 'a' from dual
union select 6 , 'b' from dual
union select 7 , 'b' from dual
),
lag_data as(
select col1, col2, lag(col2) over (order by col1) col2a from tbl
)
select col1, col2
from lag_data
where col2a is null or col2a <> col2
order by col1;
The results are:
BING @ quanghoo > with tbl as (select 1 col1, 'a' col2 from dual
2 union select 2 , 'a' from dual
3 union select 3 , 'b' from dual
4 union select 4 , 'a' from dual
5 union select 5 , 'a' from dual
6 union select 6 , 'b' from dual
7 union select 7 , 'b' from dual
8 ),
9 lag_data as(
10 select col1, col2, lag(col2) over (order by col1) col2a from tbl
11 )
12 select col1, col2
13 from lag_data
14 where col2a is null or col2a <> col2
15 order by col1;
COL1 C
---------- -
1 a
3 b
4 a
6 b
BING @ quanghoo >
David Fitzjarrell
Answers
-
Here is one way (others my have better offerings):
with tbl as (select 1 col1, 'a' col2 from dual
union select 2 , 'a' from dual
union select 3 , 'b' from dual
union select 4 , 'a' from dual
union select 5 , 'a' from dual
union select 6 , 'b' from dual
union select 7 , 'b' from dual
),
lag_data as(
select col1, col2, lag(col2) over (order by col1) col2a from tbl
)
select col1, col2
from lag_data
where col2a is null or col2a <> col2
order by col1;
The results are:
BING @ quanghoo > with tbl as (select 1 col1, 'a' col2 from dual
2 union select 2 , 'a' from dual
3 union select 3 , 'b' from dual
4 union select 4 , 'a' from dual
5 union select 5 , 'a' from dual
6 union select 6 , 'b' from dual
7 union select 7 , 'b' from dual
8 ),
9 lag_data as(
10 select col1, col2, lag(col2) over (order by col1) col2a from tbl
11 )
12 select col1, col2
13 from lag_data
14 where col2a is null or col2a <> col2
15 order by col1;
COL1 C
---------- -
1 a
3 b
4 a
6 b
BING @ quanghoo >
David Fitzjarrell
-
OU_230 wrote:I have following table:
- withtblas(select1col1,'a'col2fromdual
- unionselect2,'a'fromdual
- unionselect3,'b'fromdual
- unionselect4,'a'fromdual
- unionselect5,'a'fromdual
- unionselect6,'b'fromdual
- unionselect7,'b'fromdual
- )select*fromtblorderbycol1;
with tbl as (select 1 col1, 'a' col2 from dual union select 2 , 'a' from dual union select 3 , 'b' from dual union select 4 , 'a' from dual union select 5 , 'a' from dual union select 6 , 'b' from dual union select 7 , 'b' from dual ) select * from tbl order by col1;
I want following result i.e first value from col2 whenever col2 value changes.1 ,a3 ,b4 ,a6 ,bHow can I do this in SQL using analytical function
Is this homework? It's hard to see the usefulness of the result. Would you always want the entire result set? Or would you just want the latest 'b' or similar?
I won't bother giving any SQL, David has already shared a solution that will do one read and one sort of the data set (and very little memory will be needed for the lag), admittedly David's also sorted the result set with an order by afterwards, but that's just for niceness. Given your stated requirement, that's probably a most efficient way of getting there (there will always be other methods that do the same amount of work just slightly differently, but there's no point in finding them if you've already got a working good solution).
-
Excellent! Thanks for the help. It worked well.
-
David,
Here's a little surprise with your solution (I cut-n-pasted it to see if I could rewrite to a match_recognize) - inlining a WITH subquery taking a different plan from writing the subquery inline in the first place:
The plan for your query:
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 56 | 16 (13)| 00:00:01 |
| 1 | SORT ORDER BY | | 7 | 56 | 16 (13)| 00:00:01 |
|* 2 | VIEW | | 7 | 56 | 15 (7)| 00:00:01 |
| 3 | WINDOW SORT | | 7 | 42 | 15 (7)| 00:00:01 |
| 4 | VIEW | | 7 | 42 | 14 (0)| 00:00:01 |
| 5 | UNION-ALL | | | | | |
| 6 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 7 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 8 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 9 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 10 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 11 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 12 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("COL2A" IS NULL OR "COL2A"<>"COL2")
I had not expected to see the optimizer do the "sort order by" since it ought to have inferred that the data were already sorted correctly as a consequence of the window sort.
Here's the plan with the inline version of the code:
with tbl as (select 1 col1, 'a' col2 from dual
union all select 2 , 'a' from dual
union all select 3 , 'b' from dual
union all select 4 , 'a' from dual
union all select 5 , 'a' from dual
union all select 6 , 'b' from dual
union all select 7 , 'b' from dual
)
select col1, col2
from (
select col1, col2, lag(col2) over (order by col1) col2a from tbl
)
where col2a is null or col2a <> col2
order by col1
;
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 15 (100)| |
|* 1 | VIEW | | 7 | 56 | 15 (7)| 00:00:01 |
| 2 | WINDOW SORT | | 7 | 42 | 15 (7)| 00:00:01 |
| 3 | VIEW | | 7 | 42 | 14 (0)| 00:00:01 |
| 4 | UNION-ALL | | | | | |
| 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 6 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 7 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 8 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 9 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 10 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 11 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("COL2A" IS NULL OR "COL2A"<>"COL2"))
The effect is the same with 12.1.0.2 and 12.2.0.1
Regards
Jonathan Lewis
P.S. This might be a correct match_recognize() solution - but I still need lots of practice with the feature
with tbl as (
select 1 col1, 'a' col2 from dual
union all select 2 , 'a' from dual
union all select 3 , 'b' from dual
union all select 4 , 'a' from dual
union all select 5 , 'a' from dual
union all select 6 , 'b' from dual
union all select 7 , 'b' from dual
)
select * from tbl
match_recognize(
order by col1
measures first(col1) as startvalue, first(col2) as string_repeat
pattern(a b*)
define b as col2 = prev(col2)
)
;
-
I literally threw it together and saw that it worked; I didn't take the time to check plans like you did. There is an ORDER BY in the query so the extra sort doesn't really surprise me but it is interesting.
Thanks for taking the time, I appreciate the insight.
David Fitzjarrell