Distinct operator eliminates a duplicate only if EVERY column in the select list are the same for two rows. So if the text or note column is different, the result would include both rows.
If there are different data in the note column of two otherwise identical rows, what rule do you want to determine which of those notes to pick up? Here is a way to pick one based on alphanumeric ordering.;with cte as
(
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY number,dat ORDER BY [description],[note]) AS RN
FROM
YourTable
)
SELECT * FROM cte WHERE RN = 1;
SQL Distinct
Posted - 09/26/2012 : 11:12:18
