Akshay Hazari Akshay Hazari - 3 months ago 14
SQL Question

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.

Answer

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