How do I get only max and min value? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How do I get only max and min value?

Author

Topic

SCHEMA
Posting Yak Master

192 Posts

Posted - 2009-08-28 : 12:42:25

I have table A

COLA COLB
1 1
1 2
1 3
2 1
2 2
2 3

How do I get min and max value for Table A?
i.e

1 1
1 3
2 1
2 3

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-08-28 : 12:49:46

SELECT COLA,MIN(COLB)
FROM Table A
GROUP BY COLA
UNION ALL
SELECT COLA,MAX(COLB)
FROM Table A
GROUP BY COLA

Jim

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-08-28 : 23:11:49

Hi, This is also one method

declare @temp table ( a int, b int )
insert into @temp select 1,77
insert into @temp select 1,54
insert into @temp select 1,3
insert into @temp select 1,23
insert into @temp select 2,1
insert into @temp select 2,2
insert into @temp select 2,3

SELECT a,columnss from
( SELECT a,min(b) AS minb,max(b) AS maxb FROM @temp GROUP BY a) t
UNPIVOT ( columnss FOR [columns] IN (minb,maxb)) tt

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-29 : 11:31:24

[code]
select COLA, COLB
from (
select COLA, COLB,
min_row = row_number() over (partition by COLA order by COLB),
max_row = row_number() over (partition by COLA order by COLB desc)
from tableA
) s
where s.min_row = 1
or s.max_row = 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-31 : 01:47:19

or



select t1.* from @temp as t1 inner join
(
	select a ,min(b) as min_b, max(b) as max_b from @temp
	group by a
) as t2
on t1.a=t2.a and t1.b in (t2.min_b,t2.max_b)

Madhivanan

Failing to plan is Planning to fail

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2009-08-31 : 06:37:05

or try this on:


select * from
(
	select a, b
	from @temp
	where b >= all (select b from @temp t where t.a=a)
	union all
	select a, b
	from @temp
	where b <= all (select b from @temp t where t.a=a)
)d
group by a, b
order by a, b

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-31 : 06:42:35

quote:

Originally posted by ms65g

or try this on:


select * from
(
	select a, b
	from @temp
	where b >= all (select b from @temp t where t.a=a)
	union all
	select a, b
	from @temp
	where b <= all (select b from @temp t where t.a=a)
)d
group by a, b
order by a, b

It doesn't give the expected result

Madhivanan

Failing to plan is Planning to fail

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2009-08-31 : 07:18:58

quote:
Originally posted by madhivanan

quote:
Originally posted by ms65g

or try this on:

select * from
(
select a, b
from @temp
where b >= all (select b from @temp t where t.a=a)
union all
select a, b
from @temp
where b <= all (select b from @temp t where t.a=a)
)d
group by a, b
order by a, b

It doesn't give the expected result

Madhivanan

Failing to plan is Planning to fail

you are right.

select * from
(
	select d1.a, 
		   b=(select top 1 b from @temp t where b >= all(select b from @temp where a=d1.a) and t.a=d1.a)
	from 
	(select distinct a from @temp) as d1

	union all
	
	select d2.a, 
		   b=(select top 1 b from @temp t where b <= all(select b from @temp where a=d2.a) and t.a=d2.a)
	from 
	(select distinct a from @temp) as d2
)dd
order by a

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-31 : 08:26:37

quote:

Originally posted by ms65g

quote:
Originally posted by madhivanan

quote:
Originally posted by ms65g

or try this on:

select * from
(
select a, b
from @temp
where b >= all (select b from @temp t where t.a=a)
union all
select a, b
from @temp
where b <= all (select b from @temp t where t.a=a)
)d
group by a, b
order by a, b

It doesn't give the expected result

Madhivanan

Failing to plan is Planning to fail

you are right.

select * from
(
	select d1.a, 
		   b=(select top 1 b from @temp t where b >= all(select b from @temp where a=d1.a) and t.a=d1.a)
	from 
	(select distinct a from @temp) as d1

	union all
	
	select d2.a, 
		   b=(select top 1 b from @temp t where b <= all(select b from @temp where a=d2.a) and t.a=d2.a)
	from 
	(select distinct a from @temp) as d2
)dd
order by a

Well. Your query will take more time than other solutions

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources