CODEWITHSUNDEEP
sqlpostgresqlaggregate-functionsamazon-redshift
Akshay Hazari
Akshay Hazari

Reputation: 3267

Select sequential column records and also find the longest sequence

I wish to get a sequence of the types column having length greater than one for different ids.

The table created is as follows

 id | type 
----+------
  1 | E1
  1 | E1
  2 | A3
  3 | B2
  1 | A1
  4 | C1
  5 | C
  7 | D
  8 | D
  9 | A1
  3 | D
(11 rows)

Here is what I am trying to achieve first:

id | type
1  | E1
1  | E1
1  | A1
3  | B2
3  | D

The result above is what I should get with sequence of types E1,E1,A1 for id 1 and B2,D for id 3.

I have tried this which is undoubtedly erroneous:

select q1.id, q1.type 
from 
    (select row_number() over () as rowno, * from recs) q1, 
    (select row_number() over () as rowno, * from recs) q2 
where q1.rowno > q2.rowno and  q1.id = q2.id;`

It gives me something like:

 id | type 
----+------
  1 | E1
  1 | A1
  1 | A1
  3 | D
(4 rows)

After this I would want to find the longest sequence.

Upvotes: 0

Views: 175

Answers (2)

klin
klin

Reputation: 121804

You can use count() over partition:

select id, typ
from (
    select *, count(*) over (partition by id) seq_len
    from recs
    ) sub
where seq_len > 1

 id | typ
----+-----
  1 | A1
  1 | E1
  1 | E1
  3 | D
  3 | B2
(5 rows)

or aggregate the sequences:

select *
from (
    select id, array_agg(typ) seq
    from recs
    group by 1
    ) sub
where array_length(seq, 1) > 1

 id |    seq
----+------------
  1 | {E1,E1,A1}
  3 | {B2,D}
(2 rows)

Use the last query to select the longest sequence: 

select id, seq, array_length(seq, 1) seq_len
from (
    select id, array_agg(typ) seq
    from recs
    group by 1
    ) sub
order by 3 desc
limit 1

 id |    seq     | seq_len
----+------------+---------
  1 | {E1,E1,A1} |       3
(1 row)

Upvotes: 1

mlinth
mlinth

Reputation: 3118

Try this. The CTE gets the ids with more than one record, and the query extracts just those records.

WITH ids_recurring_more_than_once AS
(SELECT id FROM mytable GROUP BY id HAVING COUNT(*) >1)
SELECT m.* FROM mytable m
INNER JOIN ids_recurring_more_than_once 
ON m.id = ids_recurring_more_than_once.id

By "longest sequence", do you mean the id with the most recurrences? In that case, replace the CTE with:

SELECT id FROM mytable GROUP BY id ORDER BY COUNT(*) DESC LIMIT 1

Upvotes: 1

Related Questions