Prev: How to get all constraints on all tables in a DB
Next: Duplicate column name for OpenRowSet
From: Erland Sommarskog on 23 Mar 2010 18:50
(Steve Stephan) writes:
> Is there a work around for 2000 and the first function. My first
> thought was to use the Min but the explanation and solution for 2005
> made sense to me. Let me know. Thanks.

The query:

> WITH QueryB AS (
> SELECT UniqueID, StartDate, UniqueID2 as NewUniqueID,
> rn = row_number() OVER (PARTITION BY UniqueID ORDER BY
> StartDate)
> FROM QueryA
> )
> SELECT UniqueID, StartDate AS MinStartDate, NewUniqueID
> FROM QueryB
> WHERE rn = 1

Can in SQL 2000 be written as:

SELECT UniqueID, StartDate AS MinStartDate, NewUniqueID
FROM (SELECT UniqueID, StartDate, UniqueID2 as NewUniqueID,
rn = (SELECT COUNT(*)
FROM QueryA b
WHERE b.UniqueID = a.UniqueID
AND b.StartDate <= a.StartDate)
FROM QueryA a) AS QueryB
WHERE rn = 1

But note that performance of the subquery will be horrible if there is
lots of data.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: How to get all constraints on all tables in a DB
Next: Duplicate column name for OpenRowSet