get the min value - 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

get the min value

Author

Topic

a.ashabi
Posting Yak Master

117 Posts

Posted - 2009-09-10 : 17:19:03

Hi.I have 2 tables:
tbl_product
tbl_config

the join betwen them are 1 to n
means there are only one product_id on tbl_product but we have several product_id's(FK) on tbl_config

the PK for tbl_product is product_id
the PK for tbl_config is catID

I need to get catID's on tbl_config which have the lowest product_price on tbl_product.

I wrote:
SELECT MIN(dbo.tbl_product.product_price) AS price, dbo.tbl_config.product_id
FROM dbo.tbl_product INNER JOIN
dbo.tbl_config ON dbo.tbl_product.product_id = dbo.tbl_config.product_id
GROUP BY dbo.tbl_config.product_id

but when I added the catID field it gave me all the product_id's
thank u so much for yr help

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-09-11 : 02:30:17

try any one of these
SELECT MIN(p.product_price)OVER(PARTITION BY p.product_id) AS price, p.product_id, C.Catid
FROM dbo.tbl_product p INNER JOIN
dbo.tbl_config c ON p.product_id = c.product_id

select c.catid,p.product_id,p.price
from tbl_config c
inner join (select MIN(product_price) AS price, product_id
FROM dbo.tbl_product group by product_id ) p on p.product_id = c.product_id

ra.shinde
Posting Yak Master

103 Posts

Posted - 2009-09-11 : 03:30:49

Can you give some sample data?

Rahul Shinde

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources