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 |
|
slackboy13013
Starting Member
3 Posts |
 Posted - 2005-07-25 : 14:15:18
|
howto return all columns but leave out dupliates based on a certain column.EX:COL1 COL21 data12 data23 data24 data3 query should only return rows 1,2, and 4 |
|
|
nathans
Aged Yak Warrior
938 Posts |
Posted - 2005-07-25 : 15:40:40
|
How do you identify the duplicate to be removed? This will return the results you described:declare @deez table (col1 int, col2 char(5))insert into @deez select 1, 'data1' union all select 2, 'data2' union all select 3, 'data2' union all select 4, 'data3'select min(col1), col2from @deezgroup by col2 I suspect you have larger requirements.Nathan Skerl |
 |
|
|
ajthepoolman
Constraint Violating Yak Guru
384 Posts |
Posted - 2005-07-25 : 23:45:02
|
| Technically, you have no duplicates. Column 1 is unique in each record, so you will either have to exclude that column or deal with it returning "data 2" twice.Aj |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-07-26 : 01:20:26
|
| Post your expected resultMadhivananFailing to plan is Planning to fail |
|
|
|
slackboy13013
Starting Member
3 Posts |
Posted - 2005-07-26 : 10:43:56
|
I thought Nathans was the key, but I have more columns to be return and if they are not in the group by clause it fails.... Here is my table...CREATE TABLE [Segment] ( [SegmentID] [int] NULL , [SegmentNameShort] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [SegmentNameLong] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,) The problem is with a data migration process I am building. In this old table (above) there are duplicate names in the SegmentNameShort column. In our new schema (for reasons I won't bore you with), there is a unique constraint on SegmentNameShort. So what I am trying to do is do an INSERT INTO the new table, SELECTing from the old table, but I don't want multiple rows from the old table with the same value in SegmentNameShort as it violates the constraint. Right now I am just manually deleted one of the dups and keeping a record of it in a config file, but there has to be a more elegant solution.thank you for you helpJason |
|
|
|
ajthepoolman
Constraint Violating Yak Guru
384 Posts |
Posted - 2005-07-26 : 13:37:54
|
| Do you have to maintain the SegmentID into the new table or is a new one being written for each record that you are migrating? It is the ID that caused the "distinct" problem. Aj |
|
|
|
slackboy13013
Starting Member
3 Posts |
Posted - 2005-07-27 : 09:17:50
|
| Yes I need to maintain the SectionId's in the new system as there are lots of stuff linked to them, but it doesn't really matter which one is kept. I know it might violate some relations, but the ones I am really trying to get rid of are the many copies of "New Section", "New Region", etc. that these people have added probably by accident in the old krappy data entry app. It acceptable to just delete these before insert, but in trying to become a Sql guru (yeah right, lol) I thought I could figure out a nice elegant generic solution, but this one has got me, and everyone else I have asked.maybe I am looking for the ultimate one statement solution that does't exist. I guess another way to get the job done, would be to first insert the SegmentNameShort DISTICNTly, and then go back and update the rest of the data. |
|
|
|
|
|
|
|
|
howto return all columns but leave out dupliates..