SQL Server 2005 using identifier from previous join statment -

i have table 2 columns, first pkey, second identifier; ban & iid respectively.

i looking pull out top 2 iid's every distinct ban. used sub-query in max(iid) works fine. when try join table 1st statement next highest max(iid) get:

"msg 4104, level 16, state 1, line 1 multi-part identifier "ii.iid" not bound."

this referring statement in 2nd join "where iid < ii.iid"

select distinct ii.ban, ii.iid, ii2.iid     (select distinct ban, iid = max(iid) bill_timeliness..invoices with(nolock) group ban) ii     join (select distinct ban, iid = max(iid) bill_timeliness..invoices with(nolock) iid  < ii.iid group ban) ii2          on ii.ban = ii2.ban 

i have tried re-creating first part second subquery of statement.

i tried iid < max(ii.iid)

neither of these worked....i appreciate if can me out here.

-ben

this should work you:

select o.ban,         x.maxid topid,         max(case when o.iid != x.maxid o.iid else null end) secondid (     select ban, max(iid) maxid     bill_timeliness..invoices group ban) x inner join bill_timeliness..invoices o on x.ban = o.ban group o.ban,          x.maxid