Clever Geek Handbook

How to convert this query to SQL Server 2000 syntax

I am running this query for SQL Server 2008+ but it doesn't work on SQL Server 2000 and I need to execute this.

WITH CTE AS ( 
    SELECT
        custnum,
        custname,
        RN = ROW_NUMBER() OVER( PARTITION BY custnum, custname ORDER BY custnum )
    FROM
        cust
)
SELECT
    *
FROM
    CTE
WHERE RN > 1

Thank you so much for your help!

+3


source to share


2 answers


Prior to SQL Server 2005, this problem domain was resolved with ordered inserts into a table #temp

with a column IDENTITY

to generate a sequence number. This will solve the requirements RANK()

and ROW_NUMBER()

.

eg:.

    -- Create empty temp table
    SELECT custnum, custname, IDENTITY(INT, 1, 1) as RN
    INTO #cust
    FROM cust
    WHERE 0 = 1

    -- Populate with ORDER BY, to generate ascending RN
    INSERT INTO #cust
        (custnum, custname)
    SELECT
        custnum, custname
    FROM 
        cust
    ORDER BY
        custnum, custname


At this point, you can query MIN()

for each grouping custnum / custname and use that as you would use a CTE.

However ... is ROW_NUMBER()

really what you want here? Sounds like you need RANK()

, not ROW_NUMBER()

.

0


source


See if this works



select custnum,custname from
(
select (select count(*) from cust as t1 where t1.custnum<=t2.custnum) as sno,
 custnum,custname from cust as t2
) as t
where sno>1
0


source






More articles:


All Articles