Yo te di una respuesta en LWDP
Código SQL:
Ver original-- Datos en tabla de entrada
--id group val start end
--1 10 36 465 89
--2 10 35 55 11
--3 10 34 20 456
--4 20 38 1140 1177
--5 20 22 566 788
--6 20 1235 789 4796
--7 20 7894 741 1067
-- Resultado deseado
--id group val start end
-- 1 10 36 465 89
-- 3 10 34 20 456
-- 4 20 38 1140 1177
-- 7 20 7894 741 1067
DROP TABLE #test
CREATE TABLE #test ([id] INT, [GROUP] INT, [val] INT, [START] INT, [END] INT)
INSERT INTO #test VALUES(
1 ,10 ,36 ,465 ,89),
(2 ,10 ,35 ,55 ,11),
(3 ,10 ,34 ,20 ,456),
(4 ,20 ,38 ,1140 ,1177),
(5 ,20 ,22 ,566 ,788),
(6 ,20 ,1235 ,789 ,4796),
(7 ,20 ,7894 ,741 ,1067)
WITH Cte AS(
SELECT *,
RnAsc = ROW_NUMBER() OVER(PARTITION BY [GROUP] ORDER BY val),
RnDesc = ROW_NUMBER() OVER(PARTITION BY [GROUP] ORDER BY val DESC)
FROM #test
)
SELECT
id, [GROUP], val, START, [END]
FROM Cte
WHERE
RnAsc = 1 OR RnDesc = 1
ORDER BY [GROUP], val