sql server - SQL filter for lowest price -

i have huge table of pricing data, following columns

source, product, term, miles, price 

the data looks this

ll, ph, 3, 10000, 100.00 ba, ph, 3, 10000, 200.00 ll, ch, 3, 10000, 300.00 ba, ch, 3, 10000, 400.00 

i need create query filter rows show line lowest price each unique source/product/term/miles combination. in above example i'd end with:

ll, ph, 3, 10000, 100.00 ba, ph, 3, 10000, 200.00 

any appreciated. thanks.

you can use row_number() this:

sql fiddle

select     source, product, term, miles, price from(     select *,         rn = row_numer() over(partition source, product, term, miles order price)     [your_table] )t rn = 1 

or per coder of code's comment, use min() , group by:

sql fiddle

select     source, product, term, miles, price = min(price) [your_table] group source, product, term, miles