Grouping in T-SQL

Question

Can you please advise how to make this special grouping in SQL from this table

id  FromDate    ToDate          UPC         price   IsGroupSpecial  
3   2013-12-27  2013-12-30  6400000087492   315.00  1           
2   2013-12-27  2013-12-31  6400000087492   405.00  0

Need to select all with min price but the id is not necessarily minimum - the id should be taken from that row where IsGroupSpecial= 0

Answer 1

If I understood correctly

select UPC, min(price), x.id
from table t1
cross apply (select id from table t2 where t2.IsGroupSpecial =0 and t1.UPC=t2.UPC) X 
group by UPC, x.id

Answer 2

I think this type of query is what you're looking for. If you're just looking for all records that have the minimum price than a group by clause is not necessary.

select *
from table
where price = (select min(price) from table))