group by problem - 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

SQL Server 2005 Forums

Transact-SQL (2005)

group by problem

Author

Topic

xarrisx
Starting Member

3 Posts

Posted - 2010-06-22 : 04:33:51

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-22 : 04:42:32

SELECT t1.prodid, t1.prodqty,t1.prodprice
from products as t1 inner join
(
SELECT prodid, min(prodqty) as prodqty
from products
group by prodid
) as t2
on t1.prodif=t2.prodid and t1.prodqty=t2.prodqty

Madhivanan

Failing to plan is Planning to fail

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-06-22 : 04:44:40

What do you mean by 'not working'? Throws an error? (if so, what error?) Produces incorrect results? (If so, what is it returning?)

--
Gail Shaw
SQL Server MVP

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-22 : 04:44:50

or

select * from
(
select *,row_number() over (partition by prodid order by prodqty) as sno from products
) as t
where sn=1

Madhivanan

Failing to plan is Planning to fail

xarrisx
Starting Member

3 Posts

Posted - 2010-06-24 : 06:00:22

thanks a lot madhivanan
first solution working-I have to do a little research on this one(very advanced!-for me)

second solution contains duplicated values

thanks again

xarrisx
Starting Member

3 Posts

Posted - 2010-06-24 : 06:09:18

second solutionis also ok
My fault it produced duplicated values

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-24 : 06:10:25