Tuesday, July 3, 2007

Retrieving Distinct string values without using Distinct in T-SQL

I don’t know many developers to have not used Distinct in the queries at one time or the other. If you look up distinct in SQL Server Book it is defined as “Returns a set, removing duplicate tuples from a specified set.” True but not quite, when used for string column it also sorts the column alphabetically by default you like it or not.

Recently I came across a scenario where I needed to get distinct string values sorted by date time without sorting it in alphabetical order. I was dumb struck to find none of the SQL Server methods provided direct support to do so. I finally came up with the below mentioned process to achieve this, though would not say that it’s the best way forward, but till I find a better way, I am sharing the best I know.