Is there something equivalent to putting an order by clause in a derived table?

Question

This is sybase 15. Here's my problem.

I have 2 tables.

t1.jobid          t1.date
------------------------------
1                   1/1/2012
2                   4/1/2012
3                   2/1/2012
4                   3/1/2012

t2.jobid   t2.userid    t2.status
-----------------------------------------------
1              100            1
1              110            1
1              120            2
1              130            1
2              100            1
2              130            2
3              100            1
3              110            1
3              120            1
3              130            1
4              110            2
4              120            2

I want to find all the people who's status for THEIR two most recent jobs is 2.

My plan was to take the top 2 of a derived table that joined t1 and t2 and was ordered by date backwards for a given user. So the top two would be the most recent for a given user.

So that would give me that individuals most recent job numbers. Not everybody is in every job.

Then I was going to make an outer query that joined against the derived table searching for status 2's with a having a sum(status) = 4 or something like that. That would find the people with 2 status 2s.

But sybase won't let me use an order by clause in the derived table.

Any suggestions on how to go about this?

I can always write a little program to loop through all the users, but I was gonna try to make one horrendus sql out of it.

Juicy one, no?

Answer 1

You could rank the rows in the subquery by adding an extra column using a window function. Then select the rows that have the appropriate ranks within their groups.

I've never used Sybase, but the documentation seems to indicate that this is possible.

Answer 2

With Table1 As
    (
    Select 1 As jobid, '1/1/2012' As [date]
    Union All Select 2, '4/1/2012'
    Union All Select 3, '2/1/2012'
    Union All Select 4, '3/1/2012'
    )
    , Table2 As
    (
    Select 1 jobid, 100 As userid, 1 as status
    Union All Select 1,110,1
    Union All Select 1,120,2
    Union All Select 1,130,1
    Union All Select 2,100,1
    Union All Select 2,130,2
    Union All Select 3,100,1
    Union All Select 3,110,1
    Union All Select 3,120,1
    Union All Select 3,130,1
    Union All Select 4,110,2
    Union All Select 4,120,2
    )
    , MostRecentJobs As
    (
    Select T1.jobid, T1.date, T2.userid, T2.status
        , Row_Number() Over ( Partition By T2.userid Order By T1.date Desc ) As JobCnt
    From Table1 As T1
        Join Table2 As T2
            On T2.jobid = T1.jobid
    )
Select *
From MostRecentJobs As M2
Where Not Exists    (
                    Select 1
                    From MostRecentJobs As M1
                    Where M1.userid = M2.userid
                        And M1.JobCnt <= 2
                        And M1.status <> 2
                    )
     And M2.JobCnt <= 2

I'm using a number of features here which do exist in Sybase 15. First, I'm using common-table expressions both for my sample data and clump my queries together. Second, I'm using the ranking function Row_Number to order the jobs by date.

It should be noted that in the example data you gave, no user satisfies the requirement of having their two most recent jobs both be of status "2".

__

Edit

If you are using a version of Sybase that does not support ranking functions (e.g. Sybase 15 prior to 15.2), then you need simulate the ranking function using Counts.

Create Table #JobRnks
    (
    jobid int not null
    , userid int not null
    , status int not null
    , [date] datetime not null
    , JobCnt int not null
    , Primary Key ( jobid, userid, [date] )
    )

Insert #JobRnks( jobid, userid, status, [date], JobCnt )    
Select T1.jobid, T1.userid, T1.status, T1.[date], Count(T2.jobid)+ 1 As JobCnt
From    (
        Select T1.jobid, T2.userid, T2.status, T1.[date]
        From @Table2 As T2
            Join @Table1 As T1
                On T1.jobid = T2.jobid
        ) As T1
    Left Join   (
                Select T1.jobid, T2.userid, T2.status, T1.[date]
                From @Table2 As T2
                    Join @Table1 As T1
                        On T1.jobid = T2.jobid
                ) As T2
        On T2.userid = T1.userid
            And T2.[date] < T1.[date]
Group By T1.jobid, T1.userid, T1.status, T1.[date]

Select *
From #JobRnks As J1
Where Not Exists    (
                    Select 1
                    From #JobRnks As J2
                    Where J2.userid = J1.userid
                        And J2.JobCnt <= 2
                        And J2.status <> 2
                    )
    And J1.JobCnt <= 2

The reason for using the temp table here is for performance and ease of reading. Technically, you could plug in the query for the temp table into the two places used as a derived table and achieve the same result.