select * from t1 t where discount = (select min(discount) from t1 where Productid=t.Productid )
select * from table1 a where discount=(select min(discount) from table1 b where a.productid=b.productid and a.supplyid=b.supplyid)
use CTE (common table expression, only available in SQL Server 2005)create table table1 (productid varchar(10), price float, discount float, supplyid int)insert table1 select '0001', 0.5, 0.02, 1 union all select '0001', 0.4, 0.03, 2 union all select '0001', 0.4, 0.01, 3 union all select '0002', 1, 0, 1 union all select '0002', 0.8, 0, 2 union all select '0002', 0.9, 0, 3 union all select '0003', 0.8, 0.02, 1 union all select '0003', 0.7, 0.01, 2 union all select '0003', 0.8, 0.03, 3With cte AS (select row_number() over (order by productid, discount) as rowid, productid, price, discount, supplyid from table1)select c.productid, c.price, c.discount, c.supplyid from cte c JOIN (select productid, min(rowid) as rowid from cte group by productid )t ON c.productid = t.productid AND c.rowid = t.rowid>>>>>>>0001 0.4 0.01 3 0002 1 0 1 0003 0.7 0.01 2
from t1 t
where discount = (select min(discount) from t1 where Productid=t.Productid )
where discount=(select min(discount) from table1 b where a.productid=b.productid and a.supplyid=b.supplyid)
(productid varchar(10), price float, discount float, supplyid int)insert table1
select '0001', 0.5, 0.02, 1
union all select '0001', 0.4, 0.03, 2
union all select '0001', 0.4, 0.01, 3
union all select '0002', 1, 0, 1
union all select '0002', 0.8, 0, 2
union all select '0002', 0.9, 0, 3
union all select '0003', 0.8, 0.02, 1
union all select '0003', 0.7, 0.01, 2
union all select '0003', 0.8, 0.03, 3With cte
AS
(select row_number() over (order by productid, discount) as rowid, productid, price, discount, supplyid
from table1)select c.productid, c.price, c.discount, c.supplyid from cte c
JOIN
(select productid, min(rowid) as rowid from cte
group by productid
)t
ON c.productid = t.productid AND c.rowid = t.rowid>>>>>>>0001 0.4 0.01 3
0002 1 0 1
0003 0.7 0.01 2