Renaming Duplicate Row Data To Be Unique?
Sep 22, 2004
I just converted an old non-relational database into something that MS SQL likes. The old primary keys were broken up into two columns, one being useful. The column I need to use has some rows with the same values in them.
I am looking for some way in a SQL script to look for the duplicate rows and add "_X" to the data where X is a value incremented by 1 for each duplicate row found.
For example, 3 duplicate rows with "5443aa" would return "5443aa", "5443aa_1","5443aa_2".
Any ideas?
--MartinZ
View 1 Replies
ADVERTISEMENT
Oct 25, 2013
I'm designing an app for stock keeping. In my DB, I have a field called "ItemSerialNo" and I made it unique(but not the table's primary key). On my front end, I have a text box for Item Serial No and a combo box loaded with the item brands and also a save button. I know that if I try to save a serial no already existing in my DB, the app won't allow me because of the unique property of the field named "ItemSerialNo". But I want to be able to save a serial no already existing in my DB but with a different brand name.
For example, I want to be able to save information like:
1. ITEM SERIAL NO = 12345
BRAND NAME = AA
2. ITEM SERIAL NO = 12345
BRAND NAME = BB.
View 2 Replies
View Related
May 25, 2000
Hello,
This probably has been addressed before but I was unable to get the search to work properly on this site.
I am needing a script/way of deleting all rows from a DB with the exception of one record left for each row that has duplicate column data. Example :
Row 1
Field1 = 12345 Field2 =xxxxx Field 3=yyyyy Field4=zzzzz etc.
Row 2
Field1 = 12345 Field2 =zzzzzz Field 3=xxxxxx Field4=yyyyyy etc.
Row3
Field1 = 12345 Field2 =20202 Field 3=11111 Field4=zzzzz etc.
Row 4
Field1 = 54321 Field2 =xxxxx Field 3=yyyyy Field4=zzzzz etc.
Etc. Etc.
I want to be able to find the duplicates for Field1 and then delete all but 1 of those rows.( I don't care which one I keep just so only one is left.) The data in the other fields may or may not be unique.
I know how to find the duplicates it's just the deleting part I am having problems with. Any help would be much appreciated. Thanks,
Kerry
View 3 Replies
View Related
Jan 25, 2000
I have a large table that consists of the columns zip, state, city, county. The primary key "zip" has duplicates but the rows are unique.
How do I filter out only the duplicate zips.
Randy Garland
View 2 Replies
View Related
Jan 25, 2000
I have a large table that consists of the columns zip, state, city, county. The primary
key "zip" has duplicates but the rows are unique.
How do I filter out only the duplicate zips. So in effect I only have one row per unique key.
Randy Garland
if you just want a list of all rows with duplicate zipcodes then ...
SELECT * FROM TableName WHERE zip IN ( SELECT zip FROM TableName
GROUP BY zip HAVING COUNT(*)>1 )
Duncan
Duncan, I tried this but it does not return one row per key.
Randy Garland
View 3 Replies
View Related
Mar 17, 2014
Using SSE 2012 64-bit...How can I check all fields for duplicate records?I tried OVER PARTITION..But that is returning an error message
Code:
USE db
SELECT ROW_NUMBER() OVER (PARTITION BY all fields)
ORDER BY ID --Not unique) AS RowNumber
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator
View 4 Replies
View Related
Apr 3, 2000
I have 4 rows which are exactly the same. I want to delete one row but i do not have any unique identifing columns. How should i delete that row ?
View 1 Replies
View Related
Oct 25, 2004
We are running the following query, which has a unique index on Table_2 (col1 and sys1), and Column col1 from Table_1 is unique.
select top 100 s.*, x.col1
from Table_1 s
left outer join Table_2 x
on x.col1 = s.col1 and x.sys1 = 'SYSTEM0'
Unfortunately this query returns duplicate rows. And every time the result is different
But once we dbcc dbreindex the unique index on Table_2, the result will not have any dups.
Any ideas?
Thanks
Steve
View 1 Replies
View Related
Oct 2, 2015
I have a look up table with old data, which i need to truncate and load with the new set of data, however when loading I'm getting the following error
[OLE DB Destination [32]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred.
Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005Â
Description: "Cannot insert duplicate key row in object 'dbo.CaseType' with unique index 'idx_CaseType'. The duplicate key value is (49, AH).".
I know , what it means that since CaseType column has a unique index we cannot insert duplicate key, but in real world the scenarios are different , the record in question is as follows: so what is the workaround in this kind of scenario other than making it Non-unique Index?
CaseTypeID CountyID CaseCategory CaseTypeCode CaseTypeName
21 49 Probate AH Probate
48 49 Civil AH Adoption History
View 10 Replies
View Related
Dec 23, 2007
The following SP causes the error "Violation of UNIQUE KEY constraint 'AlumniID'. Cannot insert duplicate key in object [table name].
The statement has been terminated." AlumniID is the table's PK and is set to autoincrement. I'd appreciate any help or suggestions.
1 ALTER PROCEDURE dbo.sp_CreateUser
2
3 @UserID uniqueidentifier,
4 @UserName nvarchar(128),
5 @Email nvarchar(50),
6 @FirstName nvarchar(25),
7 @LastName nvarchar(50),
8 @Teacher nvarchar(25),
9 @GradYr int
10
11 AS
12 SET NOCOUNT ON;
13 --DECLARE @UserID uniqueidentifier
14 --SELECT @UserID = NULL
15 --SELECT @UserID = UserID FROM dbo.aspnet_Users WHERE LOWER(@UserName) = LoweredUserName-- AND @ApplicationId = ApplicationId
16 INSERT INTO [table]
17 (UserID,UserName,Email,FirstName,LastName,Teacher,GradYr)
18 VALUES (@UserID,@UserName,@Email,@FirstName,@LastName,@Teacher,@GradYr
View 2 Replies
View Related
Oct 2, 2000
After adding a Unique constraint to a database I cannot add more than one record with a null value for the constrained field. I've tried both adding the constraint to an empty table as well as a table with multiple null values already in the subject field; both efforts have failed.
According to BOL SQL-7 allows Unique Constraints on fields with Null values. Am I missing a step? I do need to allow nulls in the field yet ensure that when there is a non-null value it is unique.
The SQL statement I've used is: ALTER TABLE tbl_MasterUIC ADD CONSTRAINT uniquesamplenbr UNIQUE NONCLUSTERED (samplenbr)
Thanks for any and all suggestions
View 2 Replies
View Related
Oct 26, 2015
I am trying to find duplicate records based on a set of criteria, and flag one of those records as unique and the other duplicate ones as dup.
I found this suggestion:
IF( CALCULATE(Â
 COUNTROWS( Table1 );Â
 FILTER( Table1;  [Column1] = EARLIER( [Column1] ) && [Column2]
= EARLIER( [Column2]
) )Â
 ) > 1;
 "dup";
 "unique"
)
However, this method flags all duplicates as dup. how I can get to the enclosed results for one particular student?The dup check column shows the desired results. The criterion is any record with the same STUDENT_ID, PROG_DESC, FISCAL_YEAR will be considered as duplicates, but should at least have one record out of that group of duplicates flagged as 1.
STUDENT_ID| ACAD LEVEL |Â GENDER |
PROG_DESCÂ | RACEETHNICITY |
HOME_STATE | ACADEMIC_LEVELÂ | FISCAL_YEAR |
Dup Check
815581 Undergraduate
[code]....
View 6 Replies
View Related
Nov 9, 2015
We write to a log file each time a job runs. We give each job a unique batchid. I want to compare the run times of each step/record between two batch ids: '20150101888' and '20150101777'. Column Mins in the number of minutes each step ran. I am having trouble comparing the rows that have generic process and stepname – Trans Switch in this example. A new process within a batchid starts with a 'XX', 'Load'.
So I want to compare CA's Trans to CA's Tran Switch and ER's Trans Switch to ER's, etc. There can be multiple Trans Switch per process. There should be the same number between each batch, but no guarantees that something might change. Also, Trans Switch is not the record right after the new process (CA, ER) in production.
I have just made a very simplified example.
/**
Want to compare 20150101888 to 20150101777 and end up with this result set. Notice that the duplicate process/step within a process has the process (CA and ER in this example) and a sequential number added to it: 'CA Trans 1'. Need this to pull out the largest time differences.
Time difference, process, step, mins1, mins2, batchid1, batchid2
-6, CA, Load, 17, 23, 20150101888, 20150101777
0, CA Trans 1, Switch, 8, 8, 20150101888, 20150101777
-6, CA Trans 2, Switch, 9, 15, 20150101888, 20150101777
-4, ER, Load, 7, 11, 20150101888, 20150101777
-4, ER Trans 1, Switch, 7, 11, 20150101888, 20150101777
**/
[Code] ....
View 4 Replies
View Related
Mar 14, 2008
I have a table with 0 records. When I try to insert records using a SP, it gives the following error.
Violation of UNIQUE KEY Constraint 'constraint name'. Cannot insert duplicate key in object 'Objectname'.
How do I resolve this.
Thanks.
View 1 Replies
View Related
Feb 28, 2007
I have 1 client who keeps running into the following error on the subscriber and merge agents >
€œCannot insert duplicate key row in object 'MSmerge_genhistory' with unique index 'unc1MSmerge_genhistory'.€?
Last time we got this error I ran a reindex on table MSmerge_genhistory on the publisher database, I then successfully generated a new snapshot and the subscribers started to synchronize again. This time around I keep getting the error even after I follow these steps (I also ran all the jobs to clean up replication). The last time I ran into this error I created a job to reindex msmerge_genhistory on a nightly bases in an effort to avoid this problem. Can somebody please provide me with a workaround and also the reason why this error occurs in the first place.
Thank you in advanced,
Pauly C
View 6 Replies
View Related
Aug 22, 2007
Hi.
I have been recently redesigning my tables - creating FK
relationships from child tables to the PK userid in the Users table.
The specifics of what I did and why can be seen here:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1968856&SiteID=1
But, now I am getting the below error:
Cannot insert duplicate key row in object 'dbo.lastlogin' with unique index 'IX_lastlogin'.
The statement has been terminated.
Or, for that matter, SavedSearches or any other table where I need to
insert the same userid twice. I can see why I would want to avoid duplicates in the Users table. But, for lastlogin, savedsearches, and
a few of my other tables, the same user may account for multiple rows.
Any suggestions as to where I messed up and how to deal with this?
Thanks.
DBO.USERS
Code Snippet
CREATE TABLE [dbo].[users](
[userid] [int] IDENTITY(1,1) NOT NULL,
[lastname] [varchar](50) NULL,
[firstname] [varchar](50) NULL,
[email] [varchar](50) NOT NULL,
[alternateemail] [varchar](50) NULL,
[password] [varchar](50) NOT NULL,
[role] [varchar](10) NOT NULL,
[securityquestion] [varchar](50) NOT NULL,
[securityanswer] [varchar](50) NOT NULL,
[zipcode] [int] NOT NULL,
[birthmonth] [tinyint] NOT NULL,
[birthday] [tinyint] NOT NULL,
[birthyear] [int] NOT NULL,
[gender] [varchar](10) NULL,
[city] [varchar](50) NULL,
[state] [varchar](50) NULL,
[country] [varchar](50) NULL,
[registerdate] [datetime] NOT NULL,
[editdate] [datetime] NULL,
[confirmed] [bit] NULL CONSTRAINT [DF__Users__confirmed__4CC05EF3] DEFAULT ((0)),
CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED
(
[userid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_email] UNIQUE NONCLUSTERED
(
[email] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
/****** Object: Table [dbo].[lastlogin] Script Date: 08/22/2007 14:16:16 ******/
SET ANSI_NULLS ON
DBO.SAVEDSEARCHES
CREATE TABLE [dbo].[savedsearches](
[savedsearchesid] [int] IDENTITY(1,1) NOT NULL,
[searchname] [varchar](50) NOT NULL,
[userid] [int] NOT NULL,
[date] [datetime] NULL,
[isdefault] [bit] NULL,
[gender] [char](10) NULL,
[startyear] [varchar](50) NULL,
[endyear] [varchar](50) NULL,
[country] [varchar](50) NULL,
[miles] [int] NULL,
[pictures] [varchar](50) NULL,
[postal] [int] NULL,
[sort] [tinyint] NULL,
[photostring] [varchar](50) NULL,
[orderby] [tinyint] NULL,
CONSTRAINT [PK_SavedSearches] PRIMARY KEY CLUSTERED
(
[userid] ASC,
[searchname] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[savedsearches] WITH NOCHECK ADD CONSTRAINT [FK_savedsearches_users] FOREIGN KEY([userid])
REFERENCES [dbo].[users] ([userid])
GO
ALTER TABLE [dbo].[savedsearches] CHECK CONSTRAINT [FK_savedsearches_users]
GO
SET QUOTED_IDENTIFIER ON
GO
The following insert statement returned the error in the subject because userid = 32 already exists in the Users table.
INSERT INTO lastlogin
VALUES (32, CONVERT(VARCHAR(26), GETDATE(), 109), 1, CONVERT(VARCHAR(26), GETDATE(), 109))
DBO.LASTLOGIN
Code Snippet
CREATE TABLE [dbo].[lastlogin](
[lastloginid] [int] IDENTITY(1,1) NOT NULL,
[userid] [int] NOT NULL,
[date] [datetime] NOT NULL,
[status] [bit] NOT NULL CONSTRAINT [DF_lastlogin_status] DEFAULT ((0)),
[activity] [datetime] NOT NULL CONSTRAINT [DF_lastlogin_activity] DEFAULT (getutcdate()),
[online] AS (case when [status]=(1) AND datediff(minute,[activity],getutcdate())<(30) then (1) else (0) end),
CONSTRAINT [PK_lastlogin] PRIMARY KEY CLUSTERED
(
[date] ASC,
[userid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[lastlogin] WITH NOCHECK ADD CONSTRAINT [FK_lastlogin_users] FOREIGN KEY([userid])
REFERENCES [dbo].[users] ([userid])
GO
ALTER TABLE [dbo].[lastlogin] CHECK CONSTRAINT [FK_lastlogin_users]
View 1 Replies
View Related
Aug 4, 2014
I have an issue where I am getting an error on an unique index.
I know why I am getting the error but not sure how to get around it.
The query does a check on whether a unique value exists in the Insert/Select. If I run it one record at a time (SELECT TOP 1...) it works fine and just won't update it if the record exists.
But if I do it in a batch, I get the error. I assume this is because it does the checking on the file before records are written out and then writes out the records one at a time from a temporary table.
It thinks all the records are unique because it compares the records one at a time to the original table (where there would be no duplicates). But it doesn't check the records against each other. Then when it actually writes out the record, the duplicate is there.
How do I do a batch where the Insert/Select would write out the records without the duplicates as it does when I do it one record at a time.
CREATE TABLE #TestTable
(
Name varchar(50),
Email varchar (40)
)
Insert #TestTable (Name,Email) Values('Tom', 'tom@aol.com')
[Code] .....
View 1 Replies
View Related
Jan 16, 2007
We are developing a project that is expected to hold TB of data and the back end used is SQL Server 2005.
I have the following problem
I have applied Nonclustered index over a column on a table.
Designed a SP for insertion which caters for updation incase the criteria based on the input is met.
The logic goes like this
Incase there exists a row containing the value of the column that is indexed for uniqueness, there should be updation. If not there should be a new row created.
However often there is an error message that is placed above. This happens only on some of the SPs and only on rare occasions.
Can any body tell me if there is any problem with the SQL Server 2005
Thanks in advance
R Suresh, SQLDBA
View 8 Replies
View Related
May 7, 2008
Hi there ...here comes a tricky one.
I have a database table which needs to make the Index "ParentREF, UniqueName" unique - but this fails because duplicate keys are found. Thus I now need to cleanup these duplicate rows - but I cannot just delete the duplicates, because they might have rows in detail tables.
This means that all duplicate rows needs an update on the "UniqueName" value - but not the first (valid) one!
I can find those rows by
SELECT OID, UniqueName, ParentREF, CreatedUTC, ModifiedUTC FROM dbo.CmsContent AS table0
WHERE EXISTS (
SELECT OID, UniqueName, ParentREF FROM dbo.CmsContent AS table1
WHERE table0.ParentREF = table1.ParentREF
AND table0.UniqueName = table1.UniqueName
AND table0.OID != table1.OID
)
ORDER BY ParentREF, UniqueName, ModifiedUTC desc
...but I struggle to make the required SQL (SP?) to update the "invalid" rows.
Note: the "valid" row is the one with the newest ModifiedUTC value - this row must kept unchanged!
ATM the preferred (cause easiest) way is to rename the invalid rows with
UniqueName = OID
because if I use any other name I risk to create another double entry.
Thanks in advance to whoever can help me
View 4 Replies
View Related
Jan 18, 2008
A happy belated New Year to all!
Can someone please help me with the following. How do I, in SS 2000, use a value to label a column?
example
Col A Col B Col C Col D
CA '12/1/07' '11/1/07' '10/1/07'
I want to use the month and year of the date to rename the column.
Col A 12-2007 11-2007 10-2007
CA '12/1/07' '11/1/07' '10/1/07'
Thank you in advance for any help you can offer.
TMendez
View 5 Replies
View Related
Jul 27, 2007
I have a table in which a non-primary key column has a unique index on it.
If I am inserting a record into this table with a duplicate column value for the indexed column, then what will be the error number of the error in above scenario? OR How could I find this out?
View 2 Replies
View Related
Aug 10, 2000
I have data for online catalogue in SQL 7.0. The web grogrammer asked me to add a unique key for reference. I used int datatype with identity seed of 1 and increment of 1. This works fine BUT when I try to import new data I get an error because the csv file has no column and therefore no value for the unique field which will not allow null by definition.
How can I maintain a unique field to act as primary key in my data when
I want to add (and delete) data that doesn't have this field.
I tried adding the uniqueidentifier field but this gives error message.
The only work round is to delete the unigue field altogether and then add the new data and afterwards create a new unique field. At 600000 + lines of data, this is time and memory consuming
Any help appreciated,Thanks, Keith
View 1 Replies
View Related
Nov 28, 2005
I am creating a DTS package that is combining several tables, converting one column of data to a new column removing all special characters, then exporting the unique data based on this column and another column, and the max of other duplicates to a new table.
Now that I have the data in this table, I want to import any data that is not in my main table.
This "CLEANED" table does not have a designated "key" column, but the table I want to import the unique items does have an ID column that is also a primary key column.
DTS seems to want me to have a Key column to reference when importing from the CLEANED table to the MAIN table.
How would I go about checking the MAIN table against the CLEANED table, having DTS import only the unique items from the CLEANED table that are not present in the MAIN table based on three columns? The rest of the columns I want to just extract the MAX data from the duplicates.
Now here is the query I use to extract the unique values from the "CLEANING" table to get the data to the "CLEANED" table, but do not know how to use this to import into the MAIN table using something similar.
Code:
select
partno2,
MAX (partno) as partno,
alt,
MAX (C_alt) as C_alt,
Max (cmpycd) as cmpycd,
MAX (type) as type,
compFN,
MAX (pndesc) as pndesc,
MAX (equipment) as equipment
into tbl_CLEANED
from tbl_CLEANING
group by partno2, alt, compFN
ORDER BY partno, compFN
The three main columns I need to check against are:
partno2
alt
compFN
I have named the columns the same in both tables.
partno2 is the column that has been copied from partno with all special characters & spaces removed. This is the main column I am using as a reference for unique values, then if no match, I have it check against the alt column, then the comFN column. If there are no matches in any of these columns, then I want to extract the data to the MAIN table.
How can I compare these tables and import only unique info to the MAIN table?
In addition, how can I also check items that are the same in both tables and update the MAX info for the other columns (not the three I use for reference - these I need to leave alone) and update those if there is more data in the CLEANED table then in the MAIN table?
View 3 Replies
View Related
Mar 31, 2006
I have two tables which are related. The first table(A) has a sequentially assigned unique key (primary) that has a cluster index built on it. This table has roughly 1,000,000 rows of data and grows daily.
The second table(B) has a sequentially assigned unique key (primary). There is a column in table(B) which contains table(A)'s unique key. For each row in the table(A) there are roughly 30 rows in table(B).
Should I build a clustered index on the table(B) column which contains the key to table(A) or a non-clustered index?
View 6 Replies
View Related
Jan 22, 2008
Absolute SQL Beginner here
I am using SQL Server 2005 Express Edition in conjunction with asp.net 2.0
Is there any way to guarantee when INSERTing data, that all values in a given column (which is of a char data type) are unique? Or do I have to do this programatically at the asp layer?
Thanks in advance
Larry
View 4 Replies
View Related
Jul 5, 2015
This index is not unique
ix_report_history_creative_id
Msg 2601, Level 14, State 1, Procedure DFP_report_load, Line 161
Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'.
The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324).
Msg 3621, Level 0, State 0, Procedure DFP_report_load, Line 161
The statement has been terminated.
Exception in Task: Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'. The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324).
The statement has been terminated.
View 6 Replies
View Related
Dec 11, 2006
Hello guys! Is it possible to duplicate a primary key?
I would like my database to accept data with the same primary key.
Is it possible?
How do you declare ON DUPLICATE KEY UPDATE?
Please help me. Thanks in advance.
View 1 Replies
View Related
Jul 1, 2004
I want to be able to duplicate a row of data in sql....Does anyone know if there is a sql command that will do that. I have a table with an auto increment primary key and I want to duplicate everything except the key into a new record.
Thanks.
View 2 Replies
View Related
Nov 17, 1998
I am new to SQL server 6.5.
I will need to stress test a sql server 6.5 test database by duplicating
data. To do so, I need to know how to modify the primary key(which are
numbers in character data type) to duplicate the data several times over.
The primary key is character data.
What I have done ion the past is use insert statments -let's say 20 -- and then copy them and change the date in a text editor and change the primary key column and the other coluimns with thea replace of different letters and numbers. This is slow and tedious.
Does any one have a script I can run to do so?
Can I do this sql or do I need some sort of stored procedure? Any help
would be appreciated. Thanks.
DAvid Spaisman
would be greatly appreciated. THanks.
David Spaisman
View 3 Replies
View Related
Aug 28, 2007
Hello all,
I have recently been working on a project that requires one simple table to insert data into. The problem here is that all the data inserted must only access the database via stored procedure and I want to ensure that no duplicate data is inserted in the database.
I have done quite a bit of research for many ways to perform duplicate data testing from building temp tables and on, but nothing has really stood out to me yet. I would really like to find some information on how to perform duplicate data testing using a stored procedure that allows to test the data being inserted before it is saved to the database; therefore, when the user inserts the fields and clicks the insert button, the fields will be tested against the existing data (via stored procedure) within the database before being added.
Can anyone help?
Thanks
View 10 Replies
View Related
Apr 24, 2007
I have a table called emails with a field named emailaddress and some emailaddress are entered more than once. I want to be able to list all emailaddress just once. is there an sql statement that I could use to generate this.
View 3 Replies
View Related
Sep 27, 2007
Hello people, not sure how to do this. I have a unique Claim ID and when joined with the code table has 4 different Code ID and the Claim Amt is also unique to the Claim ID (One Claim Amnt per one Claim ID). I need all of these data for the report my problem is in getting the correct summation of the claim amt since this looks like I have 4 amounts of $1773.31 when in actuality there is only one. The Code ID is also part of the report parameter so I tried assigining the $ amount to just one of the Code ID but that will not work incase that particular Code ID is not selected in the parameter, the claim would read as a zero. Any help would be appreciated. Thanks
Claim ID: Code ID Claim Amt
07089000296 757.39 1773.31
07089000296 V05.3 1773.31
07089000296 V30.01 1773.31
07089000296 V72.19 1773.31
View 6 Replies
View Related
Sep 21, 2006
How would I get the unique email addresses and its associated row of data from a SQL Server table that has no unique fields defined? If there is a duplicate email address then only show the first one and not the other rows with the same email address. Example table and data UserID LastName Email997249 MCCO-49 S.MCCO-49@SampleISD.org997462 BATE-62 A.BATE-62@SampleISD.org997605 DENS-05 B.DENS-05@SampleISD.org 997622 KAIS-22 A.KAIS-22@SampleISD.org997623 KAIS-22 A.KAIS-22@SampleISD.org997624 KAIS-22 A.KAIS-22@SampleISD.org997625 KAIS-22 A.ZKAIS-22@SampleISD.org997626 KAIS-22 AX.ZKAIS-22@SampleISD.org997627 KAIS-22 AX.KAIS-22@SampleISD.org Result UserID LastName Email997249 MCCO-49 S.MCCO-49@SampleISD.org997462 BATE-62 A.BATE-62@SampleISD.org997605 DENS-05 B.DENS-05@SampleISD.org 997622 KAIS-22 A.KAIS-22@SampleISD.org997625 KAIS-22 A.ZKAIS-22@SampleISD.org997626 KAIS-22 AX.KAIS-22@SampleISD.org Thanks
View 13 Replies
View Related
