SQL - using DISTINCT on only some columns

OK - so the need arose today to use an SQL DISTINCT command, but only be distinct on some columns.

That meant that the non-distinct columns would normally more than one row, and obviously since we're asking for distinct, that data is irrelvant in this case. We could, of course, create a new view that only shows the columns we need distinct, and that's probably the best way.

However, since we have hundreds of views already, I wanted to reuse an existing view (and existing code). To to this you need to group the data by the fields that you want to be distinct. The other field's data could be any of the possible rows, so use the MAX(fieldname) function to return the highest value.

So, to get a list of all students but ignore multiple tutors (and just return one, random tutor - in fact the one with the highest name) you get something like this:

select PersonalID, Name, max(TutorName) from Table
Group By PersonalID, Name
Order By Name