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.

Group records in a Select

RumburakAug 16 2010 — edited Aug 16 2010

I want to know, if I can do this with one single Select:

I have a table where I have some Information with a date-col:

Column1  Column2  Column3  DateCol
-------
A        B        C        10/2001
A        B        C        03/2001
B        B        C        02/2001
B        B        C        01/2001
A        B        C        03/2000

Now I want to group the combinations of col1-3 but in the date order. This should be the result:

Column1  Column2  Column3  DateCol
-------
A        B        C        10/2001
B        B        C        02/2001
A        B        C        03/2000

Any ideas?
This post has been answered by Frank Kulash on Aug 16 2010
Jump to Answer

Comments

21205
that's not a DATE... 
10/2001
but you can do something like - not tested 
select col1
     , col2
, col3
, datecol
  from tbl
 group by col1
     , col2
, col3
, datecol
order by to_date (datecol, 'mm/yyyy')
Edited by: Alex Nuijten on Aug 16, 2010 5:59 PM
Rumburak

That would not work. Your Select is like a DISTINCT with an order by.

Try it:

CREATE TABLE tbl
(   col1    VARCHAR2(1),
    col2    VARCHAR2(1),
    col3    VARCHAR2(1),
    datecol DATE
);

BEGIN
    INSERT INTO tbl VALUES( 'A', 'B', 'C', TO_DATE('01.10.2001', 'DD.MM.YYYY' ));
    INSERT INTO tbl VALUES( 'A', 'B', 'C', TO_DATE('01.03.2001', 'DD.MM.YYYY' ));
    INSERT INTO tbl VALUES( 'B', 'B', 'C', TO_DATE('01.02.2001', 'DD.MM.YYYY' ));
    INSERT INTO tbl VALUES( 'B', 'B', 'C', TO_DATE('01.01.2001', 'DD.MM.YYYY' ));
    INSERT INTO tbl VALUES( 'A', 'B', 'C', TO_DATE('01.03.2000', 'DD.MM.YYYY' ));
END;  
/
Frank Kulash
Hi,

So you want one row of output for every distinct combination of col1, col2 and col3? That sounds like a job for "GROUP BY COL1, COL2, COL3".
Do you want the latest datecol in each group? That sounds like a job for "MAX datecol)" 
SELECT	  col1, col2, col3
,	  MAX (datecol)		AS datecol
FROM	  tbl
GROUP BY  col1, col2, col3
;
Do you really want 3 rows of output (2 of them with the same values of col1, col2 and col3)? If so, explain what each row of output represents.
Rumburak
It's not a simple group by question. Look at my example. The DateCol is the duration of validity of a combination.
Frank Kulash
Answer
Rumburak wrote:
It's not a simple group by question. Look at my example. The DateCol is the duration of validity of a combination.
If it's not a simple GROUP BY, then what is it? Please explain what you're trying to do.
What does each row of output represent?
Why do you want 3 rows of output, and not 1, or 2, or 4, or 5?
Why do some output rows have distinct values of col1, col2 and col3, and others do not?

It could be that you want something like this: 
WITH	got_nums	AS
(
	SELECT	col1, col2, col3
	,	datecol
	,	ROW_NUMBER () OVER ( PARTITION BY  col1, col2, col3
			      	     ORDER BY  	   datecol
				   )				AS group_num
	,	ROW_NUMBER () OVER ( ORDER BY      datecol)	AS overall_num
	FROM	tbl
)
SELECT	  col1, col2, col3
,	  TO_CHAR ( MAX (datecol)
	  	  , 'MM/YYYY'
		  )		AS datecol_month_year
FROM	  got_nums
GROUP BY  col1, col2, col3
,     	  overall_num - group_num
ORDER BY  MAX (datecol)			DESC
;
but unless I understand the problem, all I can do is guess. Guessing is not a very efficient way to work.
Marked as Answer by Rumburak · Sep 27 2020
Solomon Yakobson 
with t as (
           select 'A' c1,'B' c2,'C' c3,to_date('10/2001','mm/yyyy') dt from dual union all
           select 'A','B','C',to_date('03/2001','mm/yyyy') from dual union all
           select 'B','B','C',to_date('02/2001','mm/yyyy') from dual union all
           select 'B','B','C',to_date('01/2001','mm/yyyy') from dual union all
           select 'A','B','C',to_date('03/2000','mm/yyyy') from dual
          )
select  c1,
        c2,
        c3,
        to_char(max(dt),'mm/yyyy') dt
  from  (
         select  c1,
                 c2,
                 c3,
                 dt,
                 sum(start_of_group) over(order by dt desc) grp
           from  (
                  select  t.*,
                          case
                            when     lag(c1) over(order by dt desc) = c1
                                 and lag(c2) over(order by dt desc) = c2
                                 and lag(c3) over(order by dt desc) = c3 then 0
                            else 1
                          end start_of_group
                    from  t
                 )
        )
  group by grp,
           c1,
           c2,
           c3
  order by grp
/

C C C DT
- - - -------
A B C 10/2001
B B C 02/2001
A B C 03/2000

SQL>
SY.
Rumburak
Read the table from bottom to top in the date order (read the datecolumn as a "combination valid until"). If in the date order the combination does not change I want one record for the combination in this period.

(combination=values of col1-3)
Solomon Yakobson
Actually it is simpler:
with t as (
           select 'A' c1,'B' c2,'C' c3,to_date('10/2001','mm/yyyy') dt from dual union all
           select 'A','B','C',to_date('03/2001','mm/yyyy') from dual union all
           select 'B','B','C',to_date('02/2001','mm/yyyy') from dual union all
           select 'B','B','C',to_date('01/2001','mm/yyyy') from dual union all
           select 'A','B','C',to_date('03/2000','mm/yyyy') from dual
          )
select  c1,
        c2,
        c3,
        to_char(dt,'mm/yyyy') c4
  from  (
         select  t.*,
                 case
                   when     lag(c1) over(order by dt desc) = c1
                        and lag(c2) over(order by dt desc) = c2
                        and lag(c3) over(order by dt desc) = c3 then 0
                   else 1
                 end start_of_group
           from  t
        )
  where start_of_group = 1
  order by dt desc
/

C C C C4
- - - -------
A B C 10/2001
B B C 02/2001
A B C 03/2000

SQL>
SY.
P.S. I assume c1, c2 and c3 are not null.
1 person found this helpful
Frank Kulash
Hi,

I'm getting more confused all the time.

Point out where the last query I posted, ( 
WITH	got_nums	AS
(
	SELECT	col1, col2, col3
	,	datecol
	,	ROW_NUMBER () OVER ( PARTITION BY  col1, col2, col3
			      	     ORDER BY  	   datecol
				   )				AS group_num
	,	ROW_NUMBER () OVER ( ORDER BY      datecol)	AS overall_num
	FROM	tbl
)
SELECT	  col1, col2, col3
,	  TO_CHAR ( MAX (datecol)
	  	  , 'MM/YYYY'
		  )		AS datecol_month_year
FROM	  got_nums
GROUP BY  col1, col2, col3
,     	  overall_num - group_num
ORDER BY  MAX (datecol)			DESC
;
) gives the wrong results.
If necessary, post new sample data, and the results you want from that new sample data.
Rumburak
Yeah, it's right. You got it.

Thanks!
Aketi Jyuuzou
I like Tabibitosan method ;-)
I am reading Japanese "Tabibitosan book" now :8}
http://www.amazon.co.jp/dp/4895243702

Tabibitosan method tutorial by Aketi Jyuuzou
1007478
with t(c1,c2,c3,dt) as (
select 'A','B','C',to_date('10/2001','mm/yyyy') from dual union all
select 'A','B','C',to_date('03/2001','mm/yyyy') from dual union all
select 'B','B','C',to_date('02/2001','mm/yyyy') from dual union all
select 'B','B','C',to_date('01/2001','mm/yyyy') from dual union all
select 'A','B','C',to_date('03/2000','mm/yyyy') from dual)
select c1,c2,c3,to_char(max(dt),'yyyy-mm')
from (select c1,c2,c3,dt,
       Row_Number() over(order by dt)
      -Row_Number() over(partition by c1,c2,c3 order by dt)
      as distance
      from t)
group by c1,c2,c3,distance
order by distance desc;

C  C  C  TO_CHAR
-  -  -  -------
A  B  C  2001-10
B  B  C  2001-02
A  B  C  2000-03
783956
Hello Rumburak,

>
I want to know, if I can do this with one single Select:...
>

Given the people you got the answers from ...my guess is that it takes at least 2 select statements....

John.
1 - 12
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Sep 13 2010
Added on Aug 16 2010
12 comments
1,504 views