How to Rank By Partition with island and gap issue

Question

Is it possible to rank item by partition without use CTE method

Expected Table

item	value	ID
A	10	1
A	20	1
B	30	2
B	40	2
C	50	3
C	60	3
A	70	4
A	80	4

By giving id to the partition to allow agitated function to work the way I want.

item	MIN	MAX	ID
A	10	20	1
B	30	40	2
C	50	60	3
A	70	80	4

SQL Version: Microsoft SQL Sever 2017

Answer 1

Assuming that the value column provides the intended ordering of the records which we see in your question above, we can try using the difference in row numbers method here. Your problem is a type of gaps and islands problem.

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY value) rn1,
              ROW_NUMBER() OVER (PARTITION BY item ORDER BY value) rn2
    FROM yourTable
)

SELECT item, MIN(value) AS [MIN], MAX(value) AS [MAX], MIN(ID) AS ID
FROM cte
GROUP BY item, rn1 - rn2
ORDER BY MIN(value);

Demo

If you don't want to use a CTE here, for whatever reason, you may simply inline the SQL code in the CTE into the bottom query, as a subquery:

SELECT item, MIN(value) AS [MIN], MAX(value) AS [MAX], MIN(ID) AS ID
FROM
(
    SELECT *, ROW_NUMBER() OVER (ORDER BY value) rn1,
              ROW_NUMBER() OVER (PARTITION BY item ORDER BY value) rn2
    FROM yourTable
) t
GROUP BY item, rn1 - rn2
ORDER BY MIN(value);