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

Expected Table

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

SQL Version: Microsoft SQL Sever 2017

CodePudding user response：

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);

CodePudding user response：

You can generate group IDs by analyzing the previous row item value that could be obtained with the LAG function and finally use GROUP BY to get the minimum and maximum value in item groups.

SELECT
    item,
    MIN(value) AS "min",
    MAX(value) AS "max",
    group_id   1 AS id
FROM (    
    SELECT
        *,
        SUM(CASE WHEN item = prev_item THEN 0 ELSE 1 END) OVER (ORDER BY value) AS group_id
    FROM (    
        SELECT 
            *,
            LAG(item, 1, item) OVER (ORDER BY value) AS prev_item
        FROM t
    ) items
) groups   
GROUP BY item, group_id

Query produces output

You can check a working demo here