I have a table with 15 columns. Among these are 2 text columns - acctnum
and invnum. If any rows are the same, I want them removed. Thus,
acctnum invnum cprice amtpaid address
1234 33688 5.99 12.97 22 Hope Street
9876 33688 4.22 1.97 27 Hope Street
1234 33688 14.69 4.36 26 Hope Street
5407 33688 3.47 8.89 25 Hope Street
1234 00921 8.11 1107 24 Hope Street
2970 33688 10.05 125.97 23 Hope Street
Of the above 6 rows, all should remain except rows 1 and 3, and either 1 or
3 should remain, but not both (if doesn't matter which is removed). If I
select distinct and use the 'select...into' clause, I don't get all the
other columns of information.
Can someone help me with this?
Thanks for any help.
Bernie YaegerAh - you need a "tiebreaker". Basically another column that you can
include in your selectivity criteria to make the rows unique. For
example, if it's feasible to assume that the same acctnum/invnum is
associated with only a single address then you could say
select acctnum, invnum, cprice, amtpaid, address
from mytable as a
where address =
(select max(b.address) from mytable as b
where a.acctnum = b.acctnum
and a.invnum = b.invnum)
Any subquery that returns one of the dups would do. MAX() will give you
one of the dups. MIN() will give you a different dup. TOP 1 would
arbitrarily give you any 1 of the dups. It may be necessary to include
all the columns in the select list to get a single acctnum/invnum result.
There has been quite a bit written on tie-breakers on the web if you
want more info. Itzik Ben-Gan has recently written about them in SQLMag
(http://www.windowsitpro.com/Article...5235/45235.html) if
you're a SQL Mag subscriber. But I'm sure a google search and/or a
google groups search would return many hits regarding tiebreakers in SQL
queries.
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Bernie Yaeger wrote:
>I have a table with 15 columns. Among these are 2 text columns - acctnum
>and invnum. If any rows are the same, I want them removed. Thus,
>acctnum invnum cprice amtpaid address
>1234 33688 5.99 12.97 22 Hope Street
>9876 33688 4.22 1.97 27 Hope Street
>1234 33688 14.69 4.36 26 Hope Street
>5407 33688 3.47 8.89 25 Hope Street
>1234 00921 8.11 1107 24 Hope Street
>2970 33688 10.05 125.97 23 Hope Street
>Of the above 6 rows, all should remain except rows 1 and 3, and either 1 or
>3 should remain, but not both (if doesn't matter which is removed). If I
>select distinct and use the 'select...into' clause, I don't get all the
>other columns of information.
>Can someone help me with this?
>Thanks for any help.
>Bernie Yaeger
>
>|||Hi,
You can do this by setting rowcount. Try the following code:
SET ROWCOUNT 1
SELECT a.* FROM [tablename] a, [tablename] b
WHERE a.acctnum = b.acctnum
AND a.invnum = b.invnum
Now, although there are two different records (row 1 and row 3), the query
will return only one row.
Finally do this,
SET ROWCOUNT 0
"Mike Hodgson" wrote:
> Ah - you need a "tiebreaker". Basically another column that you can
> include in your selectivity criteria to make the rows unique. For
> example, if it's feasible to assume that the same acctnum/invnum is
> associated with only a single address then you could say
> select acctnum, invnum, cprice, amtpaid, address
> from mytable as a
> where address =
> (select max(b.address) from mytable as b
> where a.acctnum = b.acctnum
> and a.invnum = b.invnum)
> Any subquery that returns one of the dups would do. MAX() will give you
> one of the dups. MIN() will give you a different dup. TOP 1 would
> arbitrarily give you any 1 of the dups. It may be necessary to include
> all the columns in the select list to get a single acctnum/invnum result.
> There has been quite a bit written on tie-breakers on the web if you
> want more info. Itzik Ben-Gan has recently written about them in SQLMag
> (http://www.windowsitpro.com/Article...5235/45235.html) if
> you're a SQL Mag subscriber. But I'm sure a google search and/or a
> google groups search would return many hits regarding tiebreakers in SQL
> queries.
> --
> *mike hodgson* |/ database administrator/ | mallesons stephen jaques
> *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
> *E* mailto:mike.hodgson@.mallesons.nospam.com |* W* [url]http://www.mallesons.com[/url
]
>
> Bernie Yaeger wrote:
>
>|||Hi Bernie Yaeger,
I am not suggesting that following method is correct, but it surely
works.
Step 1 :- Create a table with select statement having identity column
select identity (1,1) myid,* into myTable from BaseTable
Step 2 :- Delete whatever rows you wish
delete from MyTable where myid in ( whatever)
Step 3 :- Remove the Identity column
alter table taa drop column myid
Step 4 :- Remove all records from Base Table
truncate BaseTable
Step 5:- Insert all records from MYTable
insert into BaseTable select * from MyTable
With regards
Jatinder|||Hi MIke, Deeraj, Jatinder,
Tx for all of your help. I was able to get it done with a rowid column (ide
ntity), as follows:
delete d1 from invdetbal d1 join invdetbal d2 on
d2.imcacct = d1.imcacct and d2.invnum = d1.invnum
and d1.rowid > d2.rowid
Thanks again for everyone's help.
Bernie
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:u33
7rUlaFHA.1472@.TK2MSFTNGP10.phx.gbl...
Ah - you need a "tiebreaker". Basically another column that you can include
in your selectivity criteria to make the rows unique. For example, if it's
feasible to assume that the same acctnum/invnum is associated with only a s
ingle address then you could say
select acctnum, invnum, cprice, amtpaid, address
from mytable as a
where address =
(select max(b.address) from mytable as b
where a.acctnum = b.acctnum
and a.invnum = b.invnum)
Any subquery that returns one of the dups would do. MAX() will give you one
of the dups. MIN() will give you a different dup. TOP 1 would arbitrarily
give you any 1 of the dups. It may be necessary to include all the columns
in the select list to get a single acctnum/invnum result.
There has been quite a bit written on tie-breakers on the web if you want more info. I
tzik Ben-Gan has recently written about them in SQLMag (http://www.windowsitpro.co
m...5235/45235.html) if you're a SQL Mag subscriber. But I
'm sure a google search and/or a google groups search would return many hits
regarding tiebreakers in SQL queries.
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
Bernie Yaeger wrote:
I have a table with 15 columns. Among these are 2 text columns - acctnum
and invnum. If any rows are the same, I want them removed. Thus,
acctnum invnum cprice amtpaid address
1234 33688 5.99 12.97 22 Hope Street
9876 33688 4.22 1.97 27 Hope Street
1234 33688 14.69 4.36 26 Hope Street
5407 33688 3.47 8.89 25 Hope Street
1234 00921 8.11 1107 24 Hope Street
2970 33688 10.05 125.97 23 Hope Street
Of the above 6 rows, all should remain except rows 1 and 3, and either 1 or
3 should remain, but not both (if doesn't matter which is removed). If I
select distinct and use the 'select...into' clause, I don't get all the
other columns of information.
Can someone help me with this?
Thanks for any help.
Bernie Yaeger|||Hi MIke, Deeraj, Jatinder,
Tx for all of your help. I was able to get it done with a rowid column
(identity), as follows:
delete d1 from invdetbal d1 join invdetbal d2 on
d2.imcacct = d1.imcacct and d2.invnum = d1.invnum
and d1.rowid > d2.rowid
Thanks again for everyone's help.
Bernie
"Deeraj" <Deeraj@.discussions.microsoft.com> wrote in message
news:BD5EE2B3-E11D-4713-AA5F-F2D38433FF61@.microsoft.com...
> Hi,
> You can do this by setting rowcount. Try the following code:
> SET ROWCOUNT 1
> SELECT a.* FROM [tablename] a, [tablename] b
> WHERE a.acctnum = b.acctnum
> AND a.invnum = b.invnum
> Now, although there are two different records (row 1 and row 3), the query
> will return only one row.
> Finally do this,
> SET ROWCOUNT 0
> "Mike Hodgson" wrote:
>|||Hi MIke, Deeraj, Jatinder,
Tx for all of your help. I was able to get it done with a rowid column (ide
ntity), as follows:
delete d1 from invdetbal d1 join invdetbal d2 on
d2.imcacct = d1.imcacct and d2.invnum = d1.invnum
and d1.rowid > d2.rowid
Thanks again for everyone's help.
Bernie
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:u33
7rUlaFHA.1472@.TK2MSFTNGP10.phx.gbl...
Ah - you need a "tiebreaker". Basically another column that you can include
in your selectivity criteria to make the rows unique. For example, if it's
feasible to assume that the same acctnum/invnum is associated with only a s
ingle address then you could say
select acctnum, invnum, cprice, amtpaid, address
from mytable as a
where address =
(select max(b.address) from mytable as b
where a.acctnum = b.acctnum
and a.invnum = b.invnum)
Any subquery that returns one of the dups would do. MAX() will give you one
of the dups. MIN() will give you a different dup. TOP 1 would arbitrarily
give you any 1 of the dups. It may be necessary to include all the columns
in the select list to get a single acctnum/invnum result.
There has been quite a bit written on tie-breakers on the web if you want more info. I
tzik Ben-Gan has recently written about them in SQLMag (http://www.windowsitpro.co
m...5235/45235.html) if you're a SQL Mag subscriber. But I
'm sure a google search and/or a google groups search would return many hits
regarding tiebreakers in SQL queries.
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
Bernie Yaeger wrote:
I have a table with 15 columns. Among these are 2 text columns - acctnum
and invnum. If any rows are the same, I want them removed. Thus,
acctnum invnum cprice amtpaid address
1234 33688 5.99 12.97 22 Hope Street
9876 33688 4.22 1.97 27 Hope Street
1234 33688 14.69 4.36 26 Hope Street
5407 33688 3.47 8.89 25 Hope Street
1234 00921 8.11 1107 24 Hope Street
2970 33688 10.05 125.97 23 Hope Street
Of the above 6 rows, all should remain except rows 1 and 3, and either 1 or
3 should remain, but not both (if doesn't matter which is removed). If I
select distinct and use the 'select...into' clause, I don't get all the
other columns of information.
Can someone help me with this?
Thanks for any help.
Bernie Yaeger
