1

I have an SQL table with "lines" representing elements of chained lists. I could for example have the following records:

(id, previous_id)
------------------
(1, NULL)
(2, NULL)
(3, 2)
(4, 3)
(5, NULL)
(6, 4)
(7, 5)

We have 3 lists in this table:

(1,)
(2,3,4,6)
(5,7)

I would like to find the last element of each list and the number of elements in the list. The query I am looking for would output:

last, len
1, 1
6, 4
7, 2

Is this possible in SQL?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Charles B
  • 160
  • 1
  • 12

3 Answers3

1

You can use a recursive CTE:

with recursive cte as (
      select l.previous_id as id, id as last
      from lines l
      where not exists (select 1 from lines l2 where l2.previous_id = l.id)
      union all
      select l.previous_id, cte.last
      from cte join
           lines l
           on cte.id = l.id
     )
select cte.last, count(*)
from cte
group by cte.last;

Here is a db<>fiddle.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • My first test indicates "column "cte.last" must appear in the GROUP BY clause or be used in an aggregate function" . it may be a typo from me. – Charles B Jul 02 '20 at 14:22
0
WITH RECURSIVE cte AS (
   SELECT id AS first, id AS last, 1 as len
   FROM   lines
   WHERE  previous_id IS NULL

   UNION ALL
   SELECT c.first, l.id, len + 1
   FROM   cte   c
   JOIN   lines l ON l.previous_id = c.last
   )
SELECT DISTINCT ON (first)
       last, len  -- , first -- also?
FROM   cte
ORDER  BY first, len DESC;

db<>fiddle here

Produces your result exactly.

If yo also want the first element like your title states, that's readily available.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
0

Here is an implementation in Microsoft SQL Server 2016 db<>fiddle

WITH chain
 AS (SELECT l.id AS [first], 
            l.id AS [last], 
            1 AS [len]
     FROM lines AS l
     WHERE l.previous_id IS NULL
     UNION ALL
     SELECT c.[first], 
            l.id, 
            c.[len] + 1 AS [len]
     FROM chain AS c
          JOIN lines AS l ON l.previous_id = c.[last]),
 result
 AS (SELECT DISTINCT 
            c.[first], 
            c.[last], 
            c.[len], 
            ROW_NUMBER() OVER(PARTITION BY c.[first] ORDER BY c.[len] DESC) AS rn
     FROM chain as c)
 SELECT r.[first], 
        r.[last], 
        r.[len]
 FROM result AS r
 WHERE r.rn = 1
 ORDER BY r.[first];
Charles L
  • 1