sql server 2005 - How to avoid duplicate records from group by? -

sql server 2005 - How to avoid duplicate records from group by? -

below query used find min cost of product

i update table @rates field available_count.

but result coming duplicate because of grouping by.

how update this?

set nocount on declare @products table (product_id varchar(50),product_name varchar(50) ) insert @products values ('1','pen'); insert @products values ('2','pencil'); insert @products values ('3','aschool bag'); insert @products values ('4','book'); insert @products values ('5','pencil box'); set nocount on declare @rates table (product_id varchar(50),price int, avail_count varchar(50)) insert @rates values ('1','10','1'); insert @rates values ('3','5','5'); insert @rates values ('1','5','6'); insert @rates values ('4','20','3'); insert @rates values ('4','15','2'); insert @rates values ('5','30','1'); ;with cte ( select count(*) over() total_record, p.product_id, p.product_name, isnull(min(r.price), 0) minprice, case when isnull(min(r.price), 0) > 0 1 else 0 end sortorder, r.avail_count @products p left outer bring together @rates r on r.product_id = p.product_id grouping p.product_id, p.product_name,r.avail_count ),rownum (select *,row_number() on (order sortorder desc, minprice asc,product_name) rownumber cte ) select * rownum

i not sure avail_count want show. getting duplicates because including avail_count in grouping , have different values. want avail_count value happens on same row min value? if then:

;with x ( select total_record = count(*) over(), rn = row_number() on (partition p.product_id order r.price), p.product_id, p.product_name, minprice = coalesce(r.price, 0), sortorder = case when coalesce(r.price, 0) > 0 1 else 0 end @products p left outer bring together @rates r on r.product_id = p.product_id ) select total_record, product_id, product_name, minprice, sortorder, rownumber = row_number() on (order sortorder desc, minprice, product_name) x rn = 1;

if not need show actual desired results rather explain don't want duplicates.

sql-server-2005