Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
JadeV
Yak Posting Veteran
62 Posts |
 Posted - 2012-09-26 : 11:12:18
|
| Can everyone tell me hwo to use for the query which has a lots of duplicate data.For DISTINCT, I tried to use it, it works with the number and date, but it doesn't work when there is a filed of "note" or "text" or "description" with a long text.Is there another way to check the DISTINCT data in a SQL query |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-09-26 : 11:39:49
|
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