|
|
You're in the
sql server programming
group:duplicate number
sql server programming:
Try:
select accountnr , customername , min (address) from Customers group by accountnr , customername -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada www.pinpub.com [quoted text, click to view] "Trond Hoiberg" <trond@montanis.com> wrote in message I have a table with customers that is imported from Axapta 3.0. I havenews:e0ds7O4EGHA.1032@TK2MSFTNGP11.phx.gbl... imported them into a table that is a clone of Axapta table. I see that i have rows where account number is the same. So it appears that they have inserted same customer with same accountnumber several times. So lets say the columns are: accountnr (varchar), customername (varchar), address(varchar) Typical data is then: 1000, companyA, streetA 1000, companyA, streetB 1001, companyB, streetC 1001, companyB, streetE So how can i select only one of them so that i have 1000, companyA, streetA 1001, companyB, streetC DISTINCT wont do it since street is all different. In real table there are some 93 columns so i simplifyed a lil. It appears to me that in duplicate company registrations there are only small changes. It does not matter wich one that gets selected in the SQl expression i am looking for. I have an SQL 2000 std server. best regards Trond
Well, you'll have to come up with some other business rules to break the
ties. -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada www.pinpub.com [quoted text, click to view] "Trond Hoiberg" <trond@montanis.com> wrote in message Well yes i could but i simplifyed the real case since there are so manynews:OzoFUZ4EGHA.3100@tk2msftngp13.phx.gbl... columns. In some cases also address is the same. and the only variation could appear in any of the remaining 90 columns. So i thin i have to solve it using the accountnr. Best regards Trond [quoted text, click to view] "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message news:us8cyV4EGHA.3348@TK2MSFTNGP10.phx.gbl... > Try: > > select > accountnr > , customername > , min (address) > from > Customers > group by > accountnr > , customername > > -- > Tom > > ---------------------------------------------------- > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA > SQL Server MVP > Columnist, SQL Server Professional > Toronto, ON Canada > www.pinpub.com > > "Trond Hoiberg" <trond@montanis.com> wrote in message > news:e0ds7O4EGHA.1032@TK2MSFTNGP11.phx.gbl... > I have a table with customers that is imported from Axapta 3.0. I have > imported them into a table that is a clone of Axapta table. > > I see that i have rows where account number is the same. So it appears > that > they have inserted same customer with same accountnumber several times. > > So lets say the columns are: > accountnr (varchar), customername (varchar), address(varchar) > > Typical data is then: > > 1000, companyA, streetA > 1000, companyA, streetB > 1001, companyB, streetC > 1001, companyB, streetE > > So how can i select only one of them so that i have > 1000, companyA, streetA > 1001, companyB, streetC > > DISTINCT wont do it since street is all different. In real table there are > some 93 columns so i simplifyed a lil. It appears to me that in duplicate > company registrations there are only small changes. > > It does not matter wich one that gets selected in the SQl expression i am > looking for. > > I have an SQL 2000 std server. > > best regards > Trond > > > > > >
Hi
If you had a method of uniquely identifying each row then you could do something like say an id column SELECT A.accountnr, A.customername, A.address FROM accounts A JOIN ( SELECT MIN(B.id), B.accountnr, B.customername FROM Accounts B GROUP BY B.accountnr, B.customername ) D ON A.accountnr = D.accountnr AND A.customername = D.customername Alternatively you can just use (say) MIN for each column that you are not grouping by: SELECT A.accountnr, A.customername, MIN(A.address) FROM accounts A GROUP BY A.accountnr, A.customername John [quoted text, click to view] "Trond Hoiberg" <trond@montanis.com> wrote in message news:e0ds7O4EGHA.1032@TK2MSFTNGP11.phx.gbl... >I have a table with customers that is imported from Axapta 3.0. I have >imported them into a table that is a clone of Axapta table. > > I see that i have rows where account number is the same. So it appears > that they have inserted same customer with same accountnumber several > times. > > So lets say the columns are: > accountnr (varchar), customername (varchar), address(varchar) > > Typical data is then: > > 1000, companyA, streetA > 1000, companyA, streetB > 1001, companyB, streetC > 1001, companyB, streetE > > So how can i select only one of them so that i have > 1000, companyA, streetA > 1001, companyB, streetC > > DISTINCT wont do it since street is all different. In real table there are > some 93 columns so i simplifyed a lil. It appears to me that in duplicate > company registrations there are only small changes. > > It does not matter wich one that gets selected in the SQl expression i am > looking for. > > I have an SQL 2000 std server. > > best regards > Trond > > > > >
I have a table with customers that is imported from Axapta 3.0. I have
imported them into a table that is a clone of Axapta table. I see that i have rows where account number is the same. So it appears that they have inserted same customer with same accountnumber several times. So lets say the columns are: accountnr (varchar), customername (varchar), address(varchar) Typical data is then: 1000, companyA, streetA 1000, companyA, streetB 1001, companyB, streetC 1001, companyB, streetE So how can i select only one of them so that i have 1000, companyA, streetA 1001, companyB, streetC DISTINCT wont do it since street is all different. In real table there are some 93 columns so i simplifyed a lil. It appears to me that in duplicate company registrations there are only small changes. It does not matter wich one that gets selected in the SQl expression i am looking for. I have an SQL 2000 std server. best regards Trond
Well yes i could but i simplifyed the real case since there are so many
columns. In some cases also address is the same. and the only variation could appear in any of the remaining 90 columns. So i thin i have to solve it using the accountnr. Best regards Trond [quoted text, click to view] "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message news:us8cyV4EGHA.3348@TK2MSFTNGP10.phx.gbl... > Try: > > select > accountnr > , customername > , min (address) > from > Customers > group by > accountnr > , customername > > -- > Tom > > ---------------------------------------------------- > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA > SQL Server MVP > Columnist, SQL Server Professional > Toronto, ON Canada > www.pinpub.com > > "Trond Hoiberg" <trond@montanis.com> wrote in message > news:e0ds7O4EGHA.1032@TK2MSFTNGP11.phx.gbl... > I have a table with customers that is imported from Axapta 3.0. I have > imported them into a table that is a clone of Axapta table. > > I see that i have rows where account number is the same. So it appears > that > they have inserted same customer with same accountnumber several times. > > So lets say the columns are: > accountnr (varchar), customername (varchar), address(varchar) > > Typical data is then: > > 1000, companyA, streetA > 1000, companyA, streetB > 1001, companyB, streetC > 1001, companyB, streetE > > So how can i select only one of them so that i have > 1000, companyA, streetA > 1001, companyB, streetC > > DISTINCT wont do it since street is all different. In real table there are > some 93 columns so i simplifyed a lil. It appears to me that in duplicate > company registrations there are only small changes. > > It does not matter wich one that gets selected in the SQl expression i am > looking for. > > I have an SQL 2000 std server. > > best regards > Trond > > > > > >
Hi
It does not matter if the values in the columns are the same as the minimum of two equal values will be that value. As account number is always constant (between rows you wish to differentiate) you will either have to use a different column e.g. something like the id column in my previous post or generate the means to differentiate the columns such as inserting all the values into a temporary table that has an id column. John [quoted text, click to view] "Trond Hoiberg" <trond@montanis.com> wrote in message news:OzoFUZ4EGHA.3100@tk2msftngp13.phx.gbl... > Well yes i could but i simplifyed the real case since there are so many > columns. In some cases also address is the same. and the only variation > could appear in any of the remaining 90 columns. > So i thin i have to solve it using the accountnr. > Best regards > Trond > > "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message > news:us8cyV4EGHA.3348@TK2MSFTNGP10.phx.gbl... >> Try: >> >> select >> accountnr >> , customername >> , min (address) >> from >> Customers >> group by >> accountnr >> , customername >> >> -- >> Tom >> >> ---------------------------------------------------- >> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA >> SQL Server MVP >> Columnist, SQL Server Professional >> Toronto, ON Canada >> www.pinpub.com >> >> "Trond Hoiberg" <trond@montanis.com> wrote in message >> news:e0ds7O4EGHA.1032@TK2MSFTNGP11.phx.gbl... >> I have a table with customers that is imported from Axapta 3.0. I have >> imported them into a table that is a clone of Axapta table. >> >> I see that i have rows where account number is the same. So it appears >> that >> they have inserted same customer with same accountnumber several times. >> >> So lets say the columns are: >> accountnr (varchar), customername (varchar), address(varchar) >> >> Typical data is then: >> >> 1000, companyA, streetA >> 1000, companyA, streetB >> 1001, companyB, streetC >> 1001, companyB, streetE >> >> So how can i select only one of them so that i have >> 1000, companyA, streetA >> 1001, companyB, streetC >> >> DISTINCT wont do it since street is all different. In real table there >> are >> some 93 columns so i simplifyed a lil. It appears to me that in duplicate >> company registrations there are only small changes. >> >> It does not matter wich one that gets selected in the SQl expression i am >> looking for. >> >> I have an SQL 2000 std server. >> >> best regards >> Trond >> >> >> >> >> >> > >
Hi
You may want to look at Itzik's article on assigning Row numbers to non-unique rows http://www.windowsitpro.com/Articles/ArticleID/45828/45828.html John [quoted text, click to view] "John Bell" <jbellnewsposts@hotmail.com> wrote in message news:OhAtGF5EGHA.3384@TK2MSFTNGP12.phx.gbl... > Hi > > It does not matter if the values in the columns are the same as the > minimum of two equal values will be that value. As account number is > always constant (between rows you wish to differentiate) you will either > have to use a different column e.g. something like the id column in my > previous post or generate the means to differentiate the columns such as > inserting all the values into a temporary table that has an id column. > > John > > "Trond Hoiberg" <trond@montanis.com> wrote in message > news:OzoFUZ4EGHA.3100@tk2msftngp13.phx.gbl... >> Well yes i could but i simplifyed the real case since there are so many >> columns. In some cases also address is the same. and the only variation >> could appear in any of the remaining 90 columns. >> So i thin i have to solve it using the accountnr. >> Best regards >> Trond >> >> "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message >> news:us8cyV4EGHA.3348@TK2MSFTNGP10.phx.gbl... >>> Try: >>> >>> select >>> accountnr >>> , customername >>> , min (address) >>> from >>> Customers >>> group by >>> accountnr >>> , customername >>> >>> -- >>> Tom >>> >>> ---------------------------------------------------- >>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA >>> SQL Server MVP >>> Columnist, SQL Server Professional >>> Toronto, ON Canada >>> www.pinpub.com >>> >>> "Trond Hoiberg" <trond@montanis.com> wrote in message >>> news:e0ds7O4EGHA.1032@TK2MSFTNGP11.phx.gbl... >>> I have a table with customers that is imported from Axapta 3.0. I have >>> imported them into a table that is a clone of Axapta table. >>> >>> I see that i have rows where account number is the same. So it appears >>> that >>> they have inserted same customer with same accountnumber several times. >>> >>> So lets say the columns are: >>> accountnr (varchar), customername (varchar), address(varchar) >>> >>> Typical data is then: >>> >>> 1000, companyA, streetA >>> 1000, companyA, streetB >>> 1001, companyB, streetC >>> 1001, companyB, streetE >>> >>> So how can i select only one of them so that i have >>> 1000, companyA, streetA >>> 1001, companyB, streetC >>> >>> DISTINCT wont do it since street is all different. In real table there >>> are >>> some 93 columns so i simplifyed a lil. It appears to me that in >>> duplicate >>> company registrations there are only small changes. >>> >>> It does not matter wich one that gets selected in the SQl expression i >>> am >>> looking for. >>> >>> I have an SQL 2000 std server. >>> >>> best regards >>> Trond >>> >>> >>> >>> >>> >>> >> >> > >
Trond Hoiberg (trond@montanis.com) writes:
[quoted text, click to view] > I have a table with customers that is imported from Axapta 3.0. I have > imported them into a table that is a clone of Axapta table. > > I see that i have rows where account number is the same. So it appears > that they have inserted same customer with same accountnumber several > times. > > So lets say the columns are: > accountnr (varchar), customername (varchar), address(varchar) > > Typical data is then: > > 1000, companyA, streetA > 1000, companyA, streetB > 1001, companyB, streetC > 1001, companyB, streetE > > So how can i select only one of them so that i have > 1000, companyA, streetA > 1001, companyB, streetC > > DISTINCT wont do it since street is all different. In real table there are > some 93 columns so i simplifyed a lil. It appears to me that in duplicate > company registrations there are only small changes. > > It does not matter wich one that gets selected in the SQl expression i am > looking for. Are you sure of that? What if some information is old and no longer current and some is new? From the technical presumptions you have given you can do this: ALTER TABLE tbl ADD ident int IDENTITY Then: SELECT a.accountnr, a.customername, ... FROM tbl a JOIN (SELECT accountnr, ident = MAX(ident) FROM tbl GROUP BY accountnr) AS b ON a.accountnr = b.accountnr AND a.ident = b.ident But whether this really is right from a business perspective, I am not so sure. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
August 2008
| home · blog · groups | Questions? Comments? Contact the dn |