CODEWITHSUNDEEP
sqlsql-server-2008stored-proceduresrownum
A_Sk
A_Sk

Reputation: 4630

Row_Number() returning duplicate rows

This is my query,

SELECT top 100
    UPPER(COALESCE(A.DESCR,C.FULL_NAME_ND)) AS DESCR,
    COALESCE(A.STATE, (SELECT TOP 1 STATENAME 
                       FROM M_STATEMASTER 
                       WHERE COUNTRYCODE = B.CODE)) AS STATENAME,
    COALESCE(A.STATECD, (SELECT TOP 1 CODE 
                         FROM M_STATEMASTER 
                         WHERE COUNTRYCODE = B.CODE)) AS STATECD,
    COALESCE(A.COUNTRYCD, B.CODE) AS COUNTRYCODE
FROM 
    M_CITY A
JOIN 
    M_COUNTRYMASTER B ON A.COUNTRYCD = B.CODE
JOIN 
    [GEODATASOURCE-CITIES-FREE] C ON  B.ALPHA2CODE = C.CC_FIPS
WHERE 
    EXISTS (SELECT 1 
            FROM [GEODATASOURCE-CITIES-FREE] Z 
            WHERE B.ALPHA2CODE=Z.CC_FIPS) 
ORDER BY 
    A.CODE

Perfectly working fine, but when I'm trying to get the Row_number() over(order by a.code) I'm getting the duplicate column multiple time.

e.g

SELECT top 100
    UPPER(COALESCE(A.DESCR,C.FULL_NAME_ND)) AS DESCR,
    COALESCE(A.STATE, (SELECT TOP 1 STATENAME 
                       FROM M_STATEMASTER 
                       WHERE COUNTRYCODE = B.CODE)) AS STATENAME,
    COALESCE(A.STATECD, (SELECT TOP 1 CODE 
                         FROM M_STATEMASTER 
                         WHERE COUNTRYCODE = B.CODE)) AS STATECD,
    COALESCE(A.COUNTRYCD, B.CODE) AS COUNTRYCODE
    ROW_NUMBER() OVER(ORDER BY A.CODE) AS RN -- i made a change here
FROM 
    M_CITY A
JOIN 
    M_COUNTRYMASTER B ON A.COUNTRYCD = B.CODE
JOIN 
    [GEODATASOURCE-CITIES-FREE] C ON  B.ALPHA2CODE = C.CC_FIPS
WHERE 
    EXISTS (SELECT 1 
            FROM [GEODATASOURCE-CITIES-FREE] Z 
            WHERE B.ALPHA2CODE=Z.CC_FIPS) 
ORDER BY 
    A.CODE
WHERE 
    EXISTS (SELECT 1 
            FROM [GEODATASOURCE-CITIES-FREE] Z 
            WHERE B.ALPHA2CODE = Z.CC_FIPS)

Another try, when I'm using ROW_NUMBER() OVER(ORDER BY newid()) AS RN it's taking logn time to execute.

Remember: CODE is the Pk of table M_CITY and there is no key in [GEODATASOURCE-CITIES-FREE] table.

Another thing: About JOIN(inner join), Join returns the matched Rows, right???

e.g: 

table 1 with 20 rows,
table2 with 30 rows ,
table 3 with 30 rows

If I joined these 3 table on a certain key then the possibility of getting maximum rows is 20, am I right?

Upvotes: 0

Views: 1444

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Your first query doesn't work fine. It just appears to. The reason is that you are using TOP without an ORDER BY, so an arbitrary set of 100 rows is returned.

When you add ROW_NUMBER(), the query plan changes . . . and the ordering of the result set changes as well. I would suggest that you fix the original query to use a stable sort.

Upvotes: 1

Related Questions