Data Load Based on Rank - 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

General SQL Server Forums

Database Design and Application Architecture

Data Load Based on Rank

Author

Topic

johowar1
Starting Member

1 Post

Posted - 2007-08-09 : 14:13:33

I'm fairly new to SQL and I have been asked to load multiple tables with duplicate and inconsistent data into a single table. Each source (table) has been assigned a rank in terms of the quality and reliability. After some thought, I have come up with this solution to get all of the data loaded based on rank.

Does anyone know of a better way to do this?

Value is a unique key.

SELECT *
FROM table_1

union

SELECT *
FROM table_2
where (value + '_' + yr) in
( select value + '_' + yr
from table_2
except
select value + '_' + yr
from table_1)

union

SELECT *
FROM table_3
where (value + '_' + yr) in
( select value + '_' + yr
from table_3
except
select value + '_' + yr
from table_2
except
select value + '_' + yr
from table_1)

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2007-08-21 : 10:46:21

I'm assuming (based on your use of EXCEPT) that you're using SQL Server 2005. Consequently, I'd use the ranking functions to achieve this. Note that the common table expression is only used for clarity, since it gets around having to use nested derived tables. Note also, that I've used Attribute1 to represent all common non-key attributes in your tables. In the example, I've given @TableB the highest priority, based on the value assigned to the [Precedence] attribute.


-- Declare temp tables
DECLARE @TableA TABLE ([Value] INT, [Yr] INT, Attribute1 VARCHAR(255))
DECLARE @TableB TABLE ([Value] INT, [Yr] INT, Attribute1 VARCHAR(255))
DECLARE @TableC TABLE ([Value] INT, [Yr] INT, Attribute1 VARCHAR(255))

-- Insert test data into temp tables
INSERT INTO @TableA SELECT 100, 2002, 'Table A first row'
INSERT INTO @TableA SELECT 100, 2002, 'Table A second row'
INSERT INTO @TableA SELECT 200, 2001, 'Table A first row'
INSERT INTO @TableB SELECT 100, 2002, 'Table B first row'
INSERT INTO @TableB SELECT 300, 2006, 'Table B first row'
INSERT INTO @TableC SELECT 400, 2007, 'Table C first row'
INSERT INTO @TableC SELECT 300, 2007, 'Table C first row'
INSERT INTO @TableC SELECT 100, 2002, 'Table C first row'
INSERT INTO @TableC SELECT 100, 2002, 'Table C second row';

-- Define CTE to perform union, avoiding nested derived tables
WITH UnionCTE   (   [Value], 
                    [Yr], 
                    [Attribute1], 
                    [Precedence]
                )
AS      (   SELECT  [Value], 
                    [Yr], 
                    [Attribute1], 
                    1 AS Precedence
            FROM    @TableB
            UNION ALL
            SELECT  [Value], 
                    [Yr], 
                    [Attribute1], 
                    2 AS Precedence
            FROM    @TableA
            UNION ALL
            SELECT  [Value], 
                    [Yr], 
                    [Attribute1], 
                    3 AS Precedence
            FROM    @TableC
        )

-- Select 'surviving' rows from CTE
SELECT  u.[Value], 
        u.[Yr], 
        u.[Attribute1]
FROM (  SELECT  [Value], 
                [Yr], 
                [Attribute1], 
                ROW_NUMBER() OVER(PARTITION BY [Value], [Yr] ORDER BY [Precedence]) AS Rank
        FROM    UnionCTE
    ) AS u 
WHERE u.Rank = 1

Mark

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources