I need min and max data from the group. I've written below query which will refer same table 4 times to achieve the results.Can anyone help me to minimize the table reference.
select * from @t a
where a = (select min(a) from @t b where b.grp = a.grp)
union
select * from @t a
where a = (select max(a) from @t b where b.grp = a.grp)
;
WITH MinMax
AS
(
SELECT grp, a, b
,MIN(a) OVER (PARTITION BY grp) AS Mina
,MAX(a) OVER (PARTITION BY grp) AS Maxa
FROM @t
)
SELECT grp, a, b
FROM MinMax
WHERE a IN (Mina, Maxa);
Thanks IFOR I want this without CTE because already the query is under one CTE the logic is so complex that I could touch this at the moment I want logic which will break the existing functionality.
SELECT t.*
FROM @t t
INNER JOIN (
SELECT grp, MIN(a) AS a_min, MAX(a) AS a_max
FROM @t
GROUP BY grp
) AS t_min_max ON t_min_max.grp = t.grp AND t.a IN (t_min_max.a_min, t_min_max.a_max)