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 |
|
hismightiness
Posting Yak Master
164 Posts |
 Posted - 2005-06-27 : 14:56:33
|
I am having trouble figuring out something that is probably very simple. How can I exclude duplicate records in a set of returned rows? Now, I know about the DISTINCT keyword, but in my case the records are only duplicates in the sense of how my application sees them. All fields have the same information except one field, so they are really not duplicates. Here is an example of what I am trying to do (much smaller scale and different information though)... CREATE TABLE #Temp (tblid int identity primary key, person varchar(50), co_id int, some_info varchar(55), dte_edited datetime) The "co_id" field can and should have duplicates. However, I want to only grab the earliest record in each instance of the "co_id" field. Meaning, I should not have any records where "co_id" matches another record.How should I approach this query? I may just be having a "brain fart", because I am certain this should be easy to do. :(- - - -- Will -- - - -http://www.servicerank.com/ |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2005-06-27 : 15:12:52
|
This will find the earliest record for each co_idSELECT co_id, MIN(dte_edited) As Mindte_edited FROM #Temp Group by co_id Using the handy recordset above, you can pick the recordset you want:SELECT TBLID, PERSON, CO_ID, SOME_INFO, DTE_EDITED from #temp t INNER JOIN ( -- Insert the above query here ) m on m.co_id = t.co_id AND m.Mindte_edited = t.dte_edited ORDER BY t.co_id |
 |
|
|
hismightiness
Posting Yak Master
164 Posts |
Posted - 2005-06-27 : 16:09:49
|
| What about in the (normally) rare instances where the dates are the same? Would know an example where you were first concerned about the duplicates of "co_id" and then worried about the date? This application can often have the same datetime values for multiple records.- - - -- Will -- - - -http://www.servicerank.com/ |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2005-06-27 : 23:07:19
|
quote:
Originally posted by hismightiness
What about in the (normally) rare instances where the dates are the same?
DATETIME columns store time to a granularity of 3.33 milliseconds, so I hadn't figured on duplicates.If you really have multiple rows, with the same values, and exactly the same times, and the duplicate rows must be removed, the problem becomes much more difficult...  SELECT DISTINCT t.TBLID, t.PERSON, t.CO_ID, t.SOME_INFO, t.DTE_EDITED from #temp t INNER JOIN ( -- Insert the above query here ) m on m.co_id = t.co_id AND m.Mindte_edited = t.dte_edited ORDER BY t.co_id |
|
|
|
hismightiness
Posting Yak Master
164 Posts |
Posted - 2005-06-28 : 08:28:08
|
| Does the "granularity" still stand for bulk inserts? I would theoretically have numerous rows inserted at the same time where only a single field would be different from the rest. The time should be the same (or maybe not, because of granularity?).- - - -- Will -- - - -http://www.servicerank.com/ |
|
|
|
|
|
|
|
|
Excluding Rows Based on Single Field Value