select min - max values - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

select min - max values

Author

Topic

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2008-11-10 : 14:58:56

hi

i have following data:


use tempdb
go


declare @temp table
(id_product nvarchar(10)
,id_group int
,name_group nvarchar(10)
,price float)

insert into @temp (id_product, id_group, name_group, price) values ('A123',221,'fitness',24.12)
insert into @temp (id_product, id_group, name_group, price) values ('A123',311,'kitchen',24.12)
insert into @temp (id_product, id_group, name_group, price) values ('A123',221,'fitness',12.12)
insert into @temp (id_product, id_group, name_group, price) values ('A123',221,'fitness',0.10)
insert into @temp (id_product, id_group, name_group, price) values ('A124',210,'fitness-home',13.12)
insert into @temp (id_product, id_group, name_group, price) values ('A125',213,'home',4.10)
insert into @temp (id_product, id_group, name_group, price) values ('A123',221,'fitness',26.12)
insert into @temp (id_product, id_group, name_group, price) values ('A127',311,'kitchen',10.99)

select * from @temp

i want to get out:
id_product, min(id_group), name_group, max(price).

i have code:


select

distinct t1.id_product
,min(t1.id_group)
,t1.name_group
,max(t1.price)

from @temp as t1
join (select
				id_product
				,min(id_group) as IDgroup
				,name_group
				,max(price) as MAXprice
			from @temp
			group by id_product, id_group, name_group) as t2 
		on t1.id_product = t2.id_product

group by t1.id_product,t1.name_group

but it doesn't give me right output; i want to have it first by grouped by max(price) and then by min(id_group).

thank you

Jason100
Starting Member

34 Posts

Posted - 2008-11-10 : 22:21:07

declare @temp table
(id_product nvarchar(10)
,id_group int
,name_group nvarchar(20)
,price float)

insert into @temp (id_product, id_group, name_group, price) values ('A123',221,'fitness',24.12)
insert into @temp (id_product, id_group, name_group, price) values ('A123',311,'kitchen',24.12)
insert into @temp (id_product, id_group, name_group, price) values ('A123',221,'fitness',12.12)
insert into @temp (id_product, id_group, name_group, price) values ('A123',221,'fitness',0.10)
insert into @temp (id_product, id_group, name_group, price) values ('A124',210,'fitness-home',13.12)
insert into @temp (id_product, id_group, name_group, price) values ('A125',213,'home',4.10)
insert into @temp (id_product, id_group, name_group, price) values ('A123',221,'fitness',26.12)
insert into @temp (id_product, id_group, name_group, price) values ('A127',311,'kitchen',10.99)

select * from @temp

select

distinct t1.id_product
,min(t1.id_group)
,t1.name_group
,max(t1.price)

from @temp as t1
join (select
id_product
,min(id_group) as IDgroup
,name_group
from @temp
group by id_product, id_group, name_group) as t2
on t1.id_product = t2.id_product JOIN
(select
id_product
,min(id_group) as IDgroup
,name_group
from @temp
group by id_product, id_group, name_group) as t3
on t1.id_product = t3.id_product

group by t1.id_product,t1.name_group

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-10 : 22:56:46

isnt this enough?

select id_product,name_group,min(id_group) as mingroup,max(price) as maxprice
from @temp
group by id_product,name_group

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-10 : 22:57:37

quote:
Originally posted by Jason100

declare @temp table
(id_product nvarchar(10)
,id_group int
,name_group nvarchar(20)
,price float)

insert into @temp (id_product, id_group, name_group, price) values ('A123',221,'fitness',24.12)
insert into @temp (id_product, id_group, name_group, price) values ('A123',311,'kitchen',24.12)
insert into @temp (id_product, id_group, name_group, price) values ('A123',221,'fitness',12.12)
insert into @temp (id_product, id_group, name_group, price) values ('A123',221,'fitness',0.10)
insert into @temp (id_product, id_group, name_group, price) values ('A124',210,'fitness-home',13.12)
insert into @temp (id_product, id_group, name_group, price) values ('A125',213,'home',4.10)
insert into @temp (id_product, id_group, name_group, price) values ('A123',221,'fitness',26.12)
insert into @temp (id_product, id_group, name_group, price) values ('A127',311,'kitchen',10.99)

select * from @temp

select

distinct t1.id_product
,min(t1.id_group)
,t1.name_group
,max(t1.price)

from @temp as t1
join (select
id_product
,min(id_group) as IDgroup
,name_group
from @temp
group by id_product, id_group, name_group) as t2
on t1.id_product = t2.id_product JOIN
(select
id_product
,min(id_group) as IDgroup
,name_group
from @temp
group by id_product, id_group, name_group) as t3
on t1.id_product = t3.id_product

group by t1.id_product,t1.name_group

What is difference in results from slimt_slimt query and yours?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources