sql server - SQL equivalent for First -

i understand there not equivalent works same access looking in writing query perform same task. basically, here have far:

select pr.ssn ssn,      min(pr.[last name]) [last name],     min(pr.address) [address],      min(pr.city) [city],      min(pr.state) [state],      min(pr.zip) [zip],     min(cast(pr.[pay begin period] date)) [pay begin period],     max(cast(pr.[pay end period] date)) [pay end period], payroll.dbo.[table1] pr (cast(pr.[pay begin period] date) > '1/1/2013' ,         cast(pr.[pay end period] date) < '12/31/2013') group pr.ssn 

this query retrieve earliest [pay begin period] , latest [pay end period] each ssn. however, want [last name] retrieved earliest [pay begin period]. in cases, people have [last name] change. want earliest/first 1 ever recorded.

so change line:

min(pr.[last name]) [last name], 

to this:

(select [last name]   payroll.dbo.[table1]   [pay begin period] = min([pay begin period])), 

i realize not work, best way can explain looking for.

in sql server, can enumerating rows each ssn , choosing first one:

select pr.ssn ssn,      max(case when seqnum = 1 pr.[last name] end) [last name],     min(pr.address) [address],      min(pr.city) [city],      min(pr.state) [state],      min(pr.zip) [zip],     min(cast(pr.[pay begin period] date)) [pay begin period],     max(cast(pr.[pay end period] date)) [pay end period], (select pr.*,              row_number() on (partition pr.ssn order [pay begin period]) seqnum       payroll.dbo.[table1] pr      ) pr (cast(pr.[pay begin period] date) > '1/1/2013' ,         cast(pr.[pay end period] date) < '12/31/2013') group pr.ssn; 

row_number() ranking function assigns sequential numbers rows within group. group defined partition by clause, each ssn value gets own sequence of numbers. ordering specified order by clause.