Thursday, October 18, 2012

T-SQL Select Distinct on Single Column of Multi Column Select

After a long day at work I realized I needed to do a distinct select on a integer column and read any value in the other columns. Seemed simple but was a bit of a pain to grasp the query syntax.

An example implementation:

SELECT DISTINCT Rating, MAX(Title) FROM WallpaperGallery
Group By Rating
 
Two important notes about the query. We run a distinct on [Rating] and we also need an aggregate function on the next column (can be max, min, etc.)  The second note is that the grouping need to happen on the distinct column and no other so that we have one row per distinct value in the [Rating] column. This query will disregard [Title]s that are not distinct for each distinct [Rating]. So if we have 10 rows with five distinct [Rating]s and all the titles are distinct then we will get 5 rows returned with the result of the Max() function on the Title column.

No comments:

Post a Comment