CODEWITHSUNDEEP
sqloracle-databaseoracle11gaggregate-functionslistagg
Miracle
Miracle

Reputation: 387

Oracle SQL Limit the results then display the remaining

I have a table that looks like this:

ID     YEAR    
1      2001   
1      2002    
1      2003
1      2004
1      2005
1      2006
1      2007
1      2008
2      1995   
2      1996
2      1997
2      1998

then the query that I tried:

select "ID", count("Year") "Count", listagg("Year", ', ') within group (order by "Year") "Years"
from (
        select distinct tbl2.id "ID", tbl1.year "Year"
        from table1 tbl1 join table2 tbl2
        on(tbl1.tbl2id = tbl2.id)
      )
group by "ID"

got the result like this:

ID   Count  Years
1    8      2001, 2002, 2003, 2004, 2005, 2006, 2007, 2008
2    4      1995, 1996, 1997, 1998

but what I wanted is to limit the result by 3 but display the remaining results too like this:

ID   Count  Years
1    3      2001, 2002, 2003
1    3      2004, 2005, 2006
1    2      2007, 2008
2    3      1995, 1996, 1997
2    1      1998

Upvotes: 3

Views: 145

Answers (4)

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

Use ceil function :

select "ID", count("Year") "Count", 
       listagg("Year", ', ') within group (order by "Year") "Years"
from (
        select tbl2.id "ID", tbl1.year "Year", 
               row_number() over ( partition by tbl2.id order by tbl1.year ) rn
          from table1 tbl1 join table2 tbl2
            on(tbl1.tbl2id = tbl2.id)
      )
group by "ID", ceil(rn/3);

SQL Fiddle Demo

Upvotes: 3

Nishant Gupta
Nishant Gupta

Reputation: 3656

The Solution for your problem:

CREATE OR REPLACE VIEW Tble_rnum AS
SELECT
  Table1.*,
  row_number() OVER (PARTITION BY ID ORDER BY YEAR) as rn
FROM Table1;

CREATE OR REPLACE VIEW tbl2 AS (
SELECT *
FROM Tble_rnum 
  MODEL 
   PARTITION BY (ID) 
   DIMENSION BY (rn)
   MEASURES (YEAR, LENGTHB(Year) len, 0 cum_len)
   RULES (
    cum_len[1] = LEN[1] + LENGTH(', '),
    cum_len[rn > 1] = CASE WHEN cum_len[cv()-1] + LEN[cv()] + LENGTHB(', ') <= 20
                      THEN cum_len[cv()-1] + LEN[cv()] + LENGTHB(', ')
                      ELSE LEN[cv()] + LENGTH(', ')
    END));

CREATE OR REPLACE VIEW tbl3 AS (
SELECT *
FROM tbl2
MODEL 
PARTITION BY (ID) 
DIMENSION BY (rn)
MEASURES (YEAR, len, cum_len, 0 sub_id)
   RULES (
     sub_id[1] = 1,
     sub_id[rn > 1] = CASE WHEN cum_len[cv()] = len[cv()] + LENGTHB(', ')
                           THEN sub_id[cv() - 1] + 1
                           ELSE sub_id[cv() - 1]
     END));    

SELECT
ID,
COUNT(YEAR) AS Counts,
LISTAGG(Year, ', ') WITHIN GROUP (ORDER BY YEAR) AS YEARS
FROM tbl3
group by ID, sub_id;

OUTPUT:

ID  COUNTS   YEARS
1    3       2001, 2002, 2003
1    3       2004, 2005, 2006
1    2       2007, 2008
2    3       1995, 1996, 1997
2    1       1998

For demo please follow the below Link:

http://sqlfiddle.com/#!4/85d42/27

Upvotes: 2

Fact
Fact

Reputation: 2440

Another way may be 

WITH table_x AS 
     (
SELECT 1 ID,     2001   YEAR FROM DUAL UNION ALL
SELECT 1,      2002   FROM DUAL UNION ALL  
SELECT 1 ,     2003 FROM DUAL UNION ALL
SELECT 1  ,    2004 FROM DUAL UNION ALL
SELECT 1   ,   2005 FROM DUAL UNION ALL
SELECT 1    ,  2006 FROM DUAL UNION ALL
SELECT 1     , 2007 FROM DUAL UNION ALL
SELECT 1      ,2008 FROM DUAL UNION ALL
SELECT 2      ,1995    FROM DUAL UNION ALL
SELECT 2      ,1996 FROM DUAL UNION ALL
SELECT 2      ,1997 FROM DUAL UNION ALL
SELECT 2      ,1998 FROM DUAL
)
,p as (
select id,year,mod(rownum,3) md ,rownum
    from table_x tbl1 
    order by year
    )
,q as 
(Select row_number() over (partition by id,md order by year ,id asc)     rown,p.id,year
from P order by id,year
) 
select ID, count(Year) Count, listagg(Year, ', ') within group (order by      Year) Years
from q
group by id,rown;

o/p:

1   3   2001, 2002, 2003
1   3   2004, 2005, 2006
1   2   2007, 2008
2   3   1995, 1996, 1997
2   1   1998

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269623

I would just add a row_number() and use that for aggregation:

select id, count(*) as cnt,
       listagg(year, ', ') within group (order by year) as years
from (select tbl2.id, tbl1.year,
             row_number() over (partition by tbl2.id order by tbl1.year asc) as seqnum
      from table1 tbl1 join
           table2 tbl2
           on tbl1.tbl2id = tbl2.id
      group by tbl2.id, tbl1.year
     )
group by id, floor( (seqnum - 1) / 3 )  ;

Here is a rextester showing the logic.

Upvotes: 3

Related Questions