SELECT DISTINCT on only one column from SELECT list
Posted on 2013-07-02
I have a stored proc that runs just fine but we have recently introduced a new column to the table which means that a column that was unique, (the barcode held in A.products_model in the code below), is no longer unique. The new column, (A.products_conditionID), effectively creates a composite key. This just leaves the unique SQL identity column A.products_id.
So, my question is, how can I return a set of results that are unique based only on the A.products_model column but ALSO have two additional, runtime columns of 'lowest_price' and 'highest-price' based on the A.products_sale_price column.
So, for example, let's say we have a book which we have three copies of, all 3 in different condition for sale. This would create three rows, all with the same products_model value but all with different products_conditionID values. I effectively want 1 row that includes products_model and 'lowest_price' and 'highest-price' based on the A.products_sale_price column from these 3 rows.
Obviously the stored proc will be returning multiple rows, TOP 96 to be exact.
The stored proc is curently:
SELECT DISTINCT TOP 96 A.products_id, A.products_name,
A.products_model, A.products_promopoints, A.products_sale_price, E.authorName, E.authorID
FROM dbo.tblWS_products A, dbo.tblWS_products_to_categories C, dbo.tblWS_shopCategories D, dbo.tblWS_authors E
WHERE A.products_status = 1
AND A.products_id = C.products_id
AND C.categories_id = D.catID
AND D.shopID = @shopID
AND A.products_author_id = E.authorID
ORDER BY A.products_id DESC
Help much appreciated...