checking if the values in one column are in order for the ID field using SQL-CodePudding

I want to choose one record per ID, based on the values in the sort field. if there is a gap of more than 1 in the Sort filed I would want to assign the maximum sort value for that ID field. If the sort field is in order (eg for ID B the sort filed is 9,10, 11,12) I would like to choose the minimum value for the ID.

below is the input and required output enter image description here

CodePudding user response：

Try this:

SELECT i.ID, MIN(i.sort) as sort
FROM input as i
INNER JOIN 
 (
  Select ID, MAX(diff) as gap
  FROM
   (
    SELECT ID, Sort, Rank, 
     LEAD(sort) OVER (PARTITION BY ID ORDER BY sort desc) as lead, 
     ABS(sort - LEAD(sort) OVER (PARTITION BY ID ORDER BY sort desc)) as diff 
    FROM input
   ) as a
  WHERE diff IS NOT NULL
  GROUP BY ID) as s
ON i.ID=s.ID
WHERE s.gap=1 
GROUP BY i.ID

UNION

SELECT i.ID, MAX(i.sort) as sort
FROM input as i
INNER JOIN 
 (
  Select ID, MAX(diff) as gap
  FROM
   (
    SELECT ID, Sort, Rank, 
     LEAD(sort) OVER (PARTITION BY ID ORDER BY sort desc) as lead, 
     ABS(sort - LEAD(sort) OVER (PARTITION BY ID ORDER BY sort desc)) as diff 
    FROM input
   ) as a
  WHERE diff IS NOT NULL
  GROUP BY ID) as s
ON i.ID=s.ID
WHERE s.gap<>1 
GROUP BY i.ID

I have used multiple sub-query. You can use CTE or create intermediate tables if required.

db<>fiddle link here