Prev: Organizing Solutions and Projects
Next: tuning import of large dataset
From: --CELKO-- on 6 Jun 2010 13:10
>> What I need to do is to delete one of the rows that has item_nbr = 1 and first_name = 'keith'. I don't care which one is deleted. It does NOT matter.<<

This is a realllllty bad data management policy. Please care and find
our why you are getting duplicates in your process. Mop the floor, but
always fix the leak.
From: Dan Guzman on 6 Jun 2010 18:12
> Ooooh. I like that. Ididn't know about "with" in that sense. Very neat.

The WITH clause is used to introduce what is called a common table
expression. CTEs are very useful indeed.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Keith G Hicks" <krh(a)comcast.net> wrote in message
news:Ovb#pGYBLHA.5584(a)TK2MSFTNGP06.phx.gbl...
> Ooooh. I like that. Ididn't know about "with" in that sense. Very neat.
>
>
> "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message
> news:Xns9D8EE95CAA39AYazorman(a)127.0.0.1...
>> Keith G Hicks (krh(a)comcast.net) writes:
>>> What I need to do is to delete one of the rows that has itemnum = 1 and
>>> firstname = 'keith'. I don't care which one is deleted. It does NOT
>>> matter. The resultant table needs to have itemnum and firstname unique
>>> together.
>>>
>>> What's the easiest way to do this? I was hoping to do it with a single
>>> delete statement using subqueries but I'm stuck.
>>
>> Indeed it is. Here is an example with any extra column as in difference
>> to Bob's solution:
>>
>> WITH numbered (rowno) AS (
>> SELECT row_number() OVER (PARTITION BY itemnum, firstname ORDER BY
>> notes)
>> FROM #temp
>> )
>> DELETE numbered WHERE rowno > 1
>>
>>
>> --
>> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
>>
>> Links for SQL Server Books Online:
>> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
>> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
>> SQL 2000:
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>>
>
>
From: Geoff Schaller on 6 Jun 2010 18:32
Hmmmm.... exposing your inexperience again?

Out here in the real world there are actually quite a lot of
circumstances where duplicates are quite natural. One is an auctioning
style system. Multiple entries are created against the same item but at
some point, competing values are removed, resulting in one unique
transaction. Another is a data matching service. For example name
cleaning. You start with 20 'similar' names. The process discovers that
15 are the same and renders common fields common and determines the
common 'key'. The duplicates are removed. Haven't you heard of de-duping
processes?

Geoff



"--CELKO--" <jcelko212(a)earthlink.net> wrote in message
news:3cd59f1e-eb54-470b-a3b4-3f4745e583db(a)j8g2000yqd.googlegroups.com:

> >> What I need to do is to delete one of the rows that has item_nbr = 1 and first_name = 'keith'. I don't care which one is deleted. It does NOT matter.<<
>
>
> This is a realllllty bad data management policy. Please care and find
> our why you are getting duplicates in your process. Mop the floor, but
> always fix the leak.

From: Keith G Hicks on 6 Jun 2010 19:52
LOL. My gosh man, get a life. How do you know I'm not??? You don't have
enough information to comment like that. How can you assume that we're not
dealing with this at that level??? OF COURSE I'M HAVING MY CLIENT TALK TO
THEIR CUSTOMER TO TRY TO GET THEM TO GET US CLEANER DATA!!! You have no idea
that we have to be gentle with this client's customer to keep them happy.
And in the end, we have no way of fully controlling what they do. My OP just
asked how to get rid of the duplicates. It truly doesn't matter which
duplicates I delete. And of course the duplicates are stashed away in a
holding table and emailed back to the customer so they know there's a
problem. Open mouth, insert foot. Something you must hear a lot. LOL.


"--CELKO--" <jcelko212(a)earthlink.net> wrote in message
news:3cd59f1e-eb54-470b-a3b4-3f4745e583db(a)j8g2000yqd.googlegroups.com...
>>> What I need to do is to delete one of the rows that has item_nbr = 1 and
>>> first_name = 'keith'. I don't care which one is deleted. It does NOT
>>> matter.<<
>
> This is a realllllty bad data management policy. Please care and find
> our why you are getting duplicates in your process. Mop the floor, but
> always fix the leak.



From: Geoff Schaller on 6 Jun 2010 21:29
Keith,

You have to forgive Joe's foot-In-mouth-disease. He has been out of
genuine circulation for probably over a decade now. He means well with
his tirades but they are thoroughly misguided. I've often opined that if
the entire world was able to sit back and design every data collection
container along the lines of Joe's books and dear ol' Dave's theorems,
we'd still be all working from PDP 11/45's because nothing would ever
get done. There'd be no progress.

In the real world, where there are budgets and deadlines and
compromises, all sorts of dirty data ends up on relational tables and
sooner or later, someone has the task of cleaning it up. But usually,
there is no scope to optimise or correct the underlying structures
because too much now relies on the structure. Joe doesn't understand
this - he thinks we should all go back and fix those structures. Dumb
xxxx! Joe also doesn't understand that it can be a lot cheaper to in
fact have less than perfect data structures and just spend a little bit
of money constantly maintaining them. I've seen a lot of projects never
get off the ground because the design idiots took too much time being
purists rather than pragmatists. Everything in life is a compromise: it
is our job to with the consequences.

Geoff



"Keith G Hicks" <krh(a)comcast.net> wrote in message
news:OcTllNdBLHA.1972(a)TK2MSFTNGP02.phx.gbl:

> LOL. My gosh man, get a life. How do you know I'm not??? You don't have
> enough information to comment like that. How can you assume that we're not
> dealing with this at that level??? OF COURSE I'M HAVING MY CLIENT TALK TO
> THEIR CUSTOMER TO TRY TO GET THEM TO GET US CLEANER DATA!!! You have no idea
> that we have to be gentle with this client's customer to keep them happy.
> And in the end, we have no way of fully controlling what they do. My OP just
> asked how to get rid of the duplicates. It truly doesn't matter which
> duplicates I delete. And of course the duplicates are stashed away in a
> holding table and emailed back to the customer so they know there's a
> problem. Open mouth, insert foot. Something you must hear a lot. LOL.
>
>
> "--CELKO--" <jcelko212(a)earthlink.net> wrote in message
> news:3cd59f1e-eb54-470b-a3b4-3f4745e583db(a)j8g2000yqd.googlegroups.com...
>
> >>> What I need to do is to delete one of the rows that has item_nbr = 1 and
> >>> first_name = 'keith'. I don't care which one is deleted. It does NOT
> >>> matter.<<
> >
>
> > This is a realllllty bad data management policy. Please care and find
> > our why you are getting duplicates in your process. Mop the floor, but
> > always fix the leak.

First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Organizing Solutions and Projects
Next: tuning import of large dataset