SQLTeam.com | Weblogs | Forums

Min and max data from subquery


#1

Hi,

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.

declare @t table ( grp int,a int,b char(1))

insert @t select 1,1, 'A'
insert @t select 1,2, 'B'
insert @t select 1,3, 'C'

insert @t select 2,1, 'A'
insert @t select 2,2, 'B'
insert @t select 2,6, 'F'

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)


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

#3

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.


#4
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)