How to avoid duplicates? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to avoid duplicates?

Author

Topic

astar
Starting Member

7 Posts

Posted - 2004-10-27 : 10:36:17

HI,
I have a stored procedure which is returning information about updated tables happenes beetwin some peroid of time and it looks like this:

CREATE PROCEDURE LMS_GetUpdates
(
@start datetime,
@end datetime

)
AS
SET NOCOUNT ON
CREATE TABLE #TempItems
(
ID int IDENTITY,
TransactionRef_No varchar(12),
TableName varchar(24)
)
-- Insert the rows from tblItems into the temp. table
INSERT INTO #TempItems (TransactionRef_No,TableName)

SELECT t.TransactionRef_No,'tb_LmsWeb_tracing_Import'
FROM tb_LmsWeb_tracing_Import t
WHERE t.Update_Date BETWEEN @start and @end

-- Insert the rows from tblItems into the temp. table
INSERT INTO #TempItems (TransactionRef_No,TableName)
SELECT tb.TransactionRef_No,'tb_Starting'
FROM tb_Starting tb
WHERE tb.Update_Date BETWEEN @start and @end

-- Insert the rows from tblItems into the temp. table
INSERT INTO #TempItems (TransactionRef_No,TableName)
SELECT rv.TransactionRef_No,'rv_Starting'
FROM rv_Starting rv
WHERE rv.Update_Date BETWEEN @start and @end
..............................................
................................................
................................................
................................................
SELECT distinct TransactionRef_No,TableName
FROM #TempItems
SET NOCOUNT OFF
GO

I am using distinct to avoid duplicates records.
Unfortunalty, I still have duplicates, becouse the combination of two fields is unique:

001404-00333 rv_Starting
001404-00333 tb_HouseBL
001404-00333 tb_MasterBL
001404-00333 tb_Starting
002504-00001 rv_MasterBL
002504-00002 rv_MasterBL
002504-00003 rv_MasterBL
002504-00004 rv_MasterBL
003704-01376 tb_Container
003704-01376 tb_Starting
003704-01696 tb_LmsWeb_tracing_Import
003704-01696 tb_Starting

How can I get a unique first field instead.
I understand, that if I would remove the second select field,I will have this result,but I need this field.

Alex.

dsdeming

479 Posts

Posted - 2004-10-27 : 10:50:03

You could do something like this:

SELECT TransactionRef_No, TableName = MAX( TableName )
FROM #TempItems
GROUP BY TableName

Dennis

astar
Starting Member

7 Posts

Posted - 2004-10-27 : 11:26:06

I tried this one, but I got an error:
Column #TempItems.TransactionRef_No is invalid in the select list becouse it is not contained in either an agregate function or the GROUP BY clouse.

Alex.

astar
Starting Member

7 Posts

Posted - 2004-10-27 : 11:39:11

Also remember, I need to get unique TransactionRef_No and corresponding TableName field.

Alex.

VIG
Yak Posting Veteran

86 Posts

Posted - 2004-10-27 : 11:41:39

SELECT TransactionRef_No, TableName = MAX( TableName )
FROM #TempItems
GROUP BY TransactionRef_No

astar
Starting Member

7 Posts

Posted - 2004-10-27 : 11:46:13

I got this result of stored-proc exec:

TransactionRef_No TableName
----------------- ------------------------
AT0201-00001 tb_Starting
AT0201-00002 tb_Starting
AT0501-00001 tb_Starting
AT1101-00001 tb_Starting
AT1101-00002 tb_Starting
LA0101-00001 tb_Starting
LA0301-00001 tb_Starting
LA0301-00002 tb_Starting
LA0301-00003 tb_Starting
LA0501-00001 tb_Starting
NY0101-00001 tb_Starting
NY0101-00002 tb_Starting
NY0101-00003 tb_Starting
NY0301-00001 tb_Starting
NY0301-00002 tb_Starting
NY0601-00001 tb_Starting
NY0601-00002 tb_Starting
NY0601-00003 tb_Starting
NY0801-00001 tb_Starting
NY0801-00002 tb_Starting
NY0901-00001 tb_Starting
NY0901-00002 tb_Starting
NY1001-00001 tb_Starting
NY1001-00002 tb_Starting
NY1001-00003 tb_Starting
NY1101-00001 tb_Starting
NY1101-00002 tb_Starting
NY1501-00001 tb_Starting
NY1501-00002 tb_Starting
NY1501-00003 tb_Starting

As you can see, first field is unique, but instead of receiving corresponding second field, I got the same value for all rows and this is not what I want.

dsdeming

479 Posts

Posted - 2004-10-27 : 12:04:08

Sorry I mistyped the GROUP BY. I guess I need more coffee.

Using MAX will always return tb_Starting. Do you have a hierarchy of these tables? For instance, if tb_Starting and tb_MasterBL both contain rows for the same TransactionRef_No, do you always want to display one or the other?

Dennis

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2004-10-27 : 12:09:53

Im guessing that you want the record with the maximum id number??

SELECT TransactionRef_No, TableName
FROM #TempItems
WHERE [ID] IN (SELECT MAX([ID]) FROM #TempItems GROUP BY TransactionRef_No)
GROUP BY TransactionRef_No, TableName

Andy

astar
Starting Member

7 Posts

Posted - 2004-10-27 : 12:25:05

Dennis,
All of my tables are has TransactionRef_No as unique identifier and foreign key. As long as new updates has been posted to DB - almost all tables are affected but with some exceptions. For my windows program I need to know a list of updated fields together with corresponding source table, because different tables giving to the program a different logic.

Alex.

astar
Starting Member

7 Posts

Posted - 2004-10-27 : 12:32:16

Andy,

Here is what I got with your logic:

TransactionRef_No TableName
----------------- ------------------------
AT0201-00001 tb_HouseBL
AT0201-00002 tb_HouseBL
AT0501-00001 tb_HouseBL
AT1101-00001 tb_HouseBL
AT1101-00002 tb_HouseBL
LA0101-00001 tb_HouseBL
LA0301-00001 tb_HouseBL
LA0301-00002 tb_HouseBL
LA0301-00003 tb_HouseBL
LA0501-00001 tb_HouseBL
NY0101-00001 tb_HouseBL
NY0101-00002 tb_HouseBL
NY0101-00003 tb_HouseBL
NY0301-00001 tb_HouseBL
NY0301-00002 tb_HouseBL
NY0601-00001 tb_HouseBL
NY0601-00002 tb_HouseBL
NY0601-00003 tb_HouseBL
NY0801-00001 tb_HouseBL
NY0801-00002 tb_HouseBL
NY0901-00001 tb_HouseBL
NY0901-00002 tb_HouseBL
NY1001-00001 tb_HouseBL
NY1001-00002 tb_HouseBL
NY1001-00003 tb_HouseBL
NY1101-00001 tb_HouseBL
NY1101-00002 tb_HouseBL
NY1501-00001 tb_HouseBL
NY1501-00002 tb_HouseBL
NY1501-00003 tb_HouseBL
NY1501-00004 tb_HouseBL

I do not need any agregate functions, unfortunatly, becouse it's gaving to me a wrong result. I need to fing another way to avoid DISTINCT limitations.

Alex.

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2004-10-27 : 12:50:30

Alex this is what I got using your sample data

CREATE TABLE #TempItems
(
[ID] int IDENTITY(1,1),
TransactionRef_No varchar(12),
TableName varchar(24)
)

INSERT #TempItems(TransactionRef_No, TableName)

SELECT '001404-00333' As TransactionRef_No, 'rv_Starting' AS TableName UNION
SELECT '001404-00333', 'tb_HouseBL' UNION
SELECT '001404-00333', 'tb_MasterBL' UNION
SELECT '001404-00335', 'tb_Starting' UNION
SELECT '002504-00001', 'rv_MasterBL' UNION
SELECT '002504-00002', 'rv_MasterBL' UNION
SELECT '002504-00003', 'rv_MasterBL' UNION
SELECT '002504-00004', 'rv_MasterBL' UNION
SELECT '003704-01376', 'tb_Container' UNION
SELECT '003704-01376', 'tb_Starting' UNION
SELECT '003704-01696', 'tb_LmsWeb_tracing_Import' UNION
SELECT '003704-01696', 'tb_Starting'

SELECT TransactionRef_No, TableName
FROM #TempItems
WHERE ID IN (SELECT MAX(ID) FROM #TempItems GROUP BY TransactionRef_No )
GROUP BY TransactionRef_No, TableName

DROP TABLE #TempItems

Results:
(12 row(s) affected)

TransactionRef_No TableName
----------------- ------------------------
001404-00333 tb_MasterBL
001404-00335 tb_Starting
002504-00001 rv_MasterBL
002504-00002 rv_MasterBL
002504-00003 rv_MasterBL
002504-00004 rv_MasterBL
003704-01376 tb_Starting
003704-01696 tb_Starting

(8 row(s) affected)

I think im confused now - from your original post (sample data) what would you expect to be returned by the query

astar
Starting Member

7 Posts

Posted - 2004-10-27 : 14:56:55

Andy, I am sorry for confusion, becouse you were right!
It is works just fine!

I gess that I did not save the new version of store-procedurec and tried to test it.

Thank a lot, man!

Alex.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources