?
Solved

ROW_NUMBER and PARTITION MS SQL Server  2000 translation

Posted on 2007-09-28
3
Medium Priority
?
3,293 Views
Last Modified: 2008-01-09
I have a query (below) that uses the row_number and partition functions which are supported by MS SQL Server 2005, but not 2000.  Unfortunately, one of the servers I query against is a 2000 server.  How can I write the query below to work in 2000 with the same results?

select SalesNum,
CASE ROW_NUMBER() OVER (PARTITION BY a.LoanNum ORDER BY a.LoanNum)
WHEN 1 THEN 'Y'
ELSE 'N'
END AS FirstRecord
FROM
Table2

SaleNum     FirstRecord
1                          Y
1                          N
2                          Y
3                          Y
3                          N
3                          N
4                          Y
4                          N
4                          N
4                          N
5                          Y
5                          N


CASE ROW_NUMBER() OVER (PARTITION BY a.LoanNum ORDER BY a.LoanNum)
WHEN 1 THEN 'Y'
ELSE 'N'
END AS FirstOccurrence,
0
Comment
Question by:srejja
3 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 19980389
You will have to first write to a temporary table (or variable of type table) that contains an IDENTITY column.  You can then output the results from that temporary table.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19981890
assuming you have some primary key on the table, and it is actually LoadNum, then you can emulate the row_number() function


select salesNum
, case when Row_number = 0 then 'Y' else 'N' end as firstrecord
from (
select SalesNum,
( select count(*) from Table2 i where i.SaleNum = t.SaleNum and i.LoanNum  < t.LoanNum ) as row_number
FROM Table2 t
) as l


hope this helps
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 19994813
You don't require correlated subquery(ies) [or a temp table] just to flag the first row, something like this should do it:


select Table2.SalesNum, CASE WHEN Table2.LoanNum = Table2Min.LoanNum THEN 'Y' ELSE 'N' END AS FirstRecord
FROM
Table2
INNER JOIN (
    SELECT SalesNum, MIN(LoanNum) AS LoanNum
    FROM Table2
    GROUP BY SalesNum
) AS Table2Min ON Table2.salesNum = Table2Min.salesNum
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question