SQL-92 Query to find earliest date dependent on column value changing

Question

I am querying a data system with an OLEDB interface that supports SQL92. My query problem is equivalent to the one solved here: http://stackoverflow.com/questions/2034094/sql-query-to-find-earliest-date-dependent-on-column-value-changing, but the solution provided there and copied below is too advanced for SQL92:

SELECT JobCodeId, MIN(LastEffectiveDate) AS mindate 
FROM    ( 
        SELECT  *, 
                prn - rn AS diff 
        FROM    ( 
                SELECT  *, 
                        ROW_NUMBER() OVER (PARTITION BY JobCodeID  
                                    ORDER BY LastEffectiveDate) AS prn, 
                        ROW_NUMBER() OVER (ORDER BY LastEffectiveDate) AS rn 
                FROM    @tmp 
                ) q 
        ) q2 
GROUP BY 
        JobCodeId, diff 
ORDER BY 
        mindate 

What would a SQL92-compliant version of this solution look like?

Answer 1

Use:

SELECT JobCodeId, 
       MIN(LastEffectiveDate) AS mindate 
  FROM (SELECT  *, 
                prn - rn AS diff 
           FROM (SELECT *,  
                        (SELECT CASE WHEN COUNT(*) = 0 THEN 1 ELSE COUNT(*) END
                           FROM @tmp t
                          WHERE t.JobCodeID = r.JobCodeID
                            AND t.LastEffectiveDate <= x.LastEffectiveDate) AS prn,
                        (SELECT COUNT(*) + 1
                           FROM @tmp t
                          WHERE t.LastEffectiveDate <= x.LastEffectiveDate) AS rn
                   FROM @tmp x) q 
       ) q2 
GROUP BY JobCodeId, diff 
ORDER BY mindate