SQL Server Performance Forum – Threads Archive
to check the data identical
Hi alli have a table containing lastname,firstname,salution,address
in the table above need to be checked whether lastname,firstname,salution is same and data
is repeating check all the above records are from same address if that is also matches the
record should be deleted otherwise if the address is not matching the address table to be
created and insert these records in to address table
please help me sql server
Can you please provide some sample data and what is should be checked for? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)
You could use something like this – as long as you don’t really care which address is taken: SELECT lastname, firstname, salution, MAX(address)
FROM MyTable
GROUP BY lastname, firstname, salution If you have a LastUpdate column that shows when the row was last touched, you can find the most recently updated row like this: SELECT T1.lastname, T1.firstname, T1.salution, T1.address
FROM MyTable T1
WHERE T1.LastUpdate =
(SELECT MAX(T2.LastUpdate) FROM MyTable T2
WHERE T2.lastname = T1.lastname AND T2.firstname = t.firstname AND T2.salution = T1.salution) Ideally, your table would have an IDENTITY column that has been there from the start of data entry. In that case, the value on the column is guaranteed to be unique, which is not the case for LastUpdate. In this last query, you could then replace "LastUpdate" with the name of the identity column.
hi Adriaan thanks for the reply
actual records are 8581 and when i execute this query SELECT lastname, firstname, salution, MAX(address)
FROM MyTable
GROUP BY lastname, firstname, salution
the records are displayed 8391 if i want to get all remaining 190 records sql server
Hi frankkalis data would be something like this LASTNAMEFIRSTNAMESALUTATIONADDRESSCITYSTATEZIP TOTALCONTRIBUTIONS DATE_OF_CONTRIBUTIONSOURCERECORD_SOURCE
LancasterAlvisMr. Alvis E. Lancaster2408 Dellwood DriveDurhamNC27705 $50 1/3/1998State98"2001-1998State
RepublicanDonors.xls"
McLachlanJamesMr. James C. McLachlan, Jr.3400 Kirklees RoadWinston SalemNC27104 $50 1/3/1998State98"2001-1998State
RepublicanDonors.xls"
DowdRobertMr. Robert A. Dowd125 College Walk LaneBrevardNC28712 $100 1/12/1998State98"2001-1998State
RepublicanDonors.xls"
sql server
Your table has 8391 different combinations of lastname/firstname/salution. You can probably drop the salution column: SELECT lastname, firstname, MAX(salution), MAX(address)
FROM MyTable
GROUP BY lastname, firstname
hi adriaan
this time i got 8231 records.I want the query to retrieve the remaining leftout rows how can i get them? please tell me Thanks in advance sql server
This will return all rows that share a lastname + firstname … SELECT X.*
FROM MyTable X
INNER JOIN
(SELECT lastname, firstname
FROM MyTable
GROUP BY lastname, firstname
HAVING COUNT(*) > 1) Y
ON X.lastname = Y.lastname and X.firstname = Y.firstname
ORDER BY X.lastname, X.firstname The salution and address values taken with the MAX() function would both be the "highest" in alphabetical order, not necessarily from the same row in the source table.
hi adriaan
The salution and address values taken with the MAX() function would both be the "highest" in alphabetical order – Yes , not necessarily from the same row in the source table. i am writing the procedure as you told me in the following manner USE testdb;
GO
IF OBJECT_ID ( ‘usp_getallrec’, ‘P’ ) IS NOT NULL
DROP PROCEDURE usp_getallrec;
GO
CREATE PROCEDURE usp_getallrec
AS
SELECT lastname, firstname, salution, MAX(address)
FROM MyTable
GROUP BY lastname, firstname, salution
GO
SELECT X.*
FROM MyTable X
INNER JOIN
(SELECT lastname, firstname
FROM MyTable
GROUP BY lastname, firstname
HAVING COUNT(*) > 1) Y
ON X.lastname = Y.lastname and X.firstname = Y.firstname
ORDER BY X.lastname, X.firstname
after completion of the writing above procedure the records to be deleted are getting displayed i should not display it please correct my procedure They should be displayed when i execute the query and below that i have to have a delete statement to delete them thanks for giving me good support adriaan
I think you need to normalize the table. For master client index formulation, you should have person(alias table) and address table. Your person should unique id. Use a view if you need to have a combination of the person and address. Check the TYPE1, TYPE2 and TYPE3 ETL in a data warehouse setting. It seems you need one of these types. May the Almighty God bless us all!
www.empoweredinformation.com
Hi
I have created the stored procedure like this but i need the records generated by the following query should not be displayed and to be deleted anyone plz help me SELECT X.*
FROM MyTable X
INNER JOIN
(SELECT lastname, firstname
FROM MyTable
GROUP BY lastname, firstname
HAVING COUNT(*) > 1) Y
ON X.lastname = Y.lastname and X.firstname = Y.firstname
ORDER BY X.lastname, X.firstname USE testdb;
GO
IF OBJECT_ID ( ‘usp_getallrec’, ‘P’ ) IS NOT NULL
DROP PROCEDURE usp_getallrec;
GO
CREATE PROCEDURE usp_getallrec
AS
SELECT lastname, firstname, salution, MAX(address)
FROM MyTable
GROUP BY lastname, firstname, salution
GO
SELECT X.*
FROM MyTable X
INNER JOIN
(SELECT lastname, firstname
FROM MyTable
GROUP BY lastname, firstname
HAVING COUNT(*) > 1) Y
ON X.lastname = Y.lastname and X.firstname = Y.firstname
ORDER BY X.lastname, X.firstname
sql server
]]>