try this,select userid,realname,snname,sequencenumber from (select u.id userid, u.realname, cs.snname, cs.sequencenumber, row_number() over(partition by u.id order by cs.sequencenumber desc) 'rn' from user_info u left join check_info c on c.userid=u.id left join checksn cs on c.checksnid=cs.id where u.schoolid=7 and u.schoolyear=2010 and u.schoolclass=1) t where rn=1 order by sequencenumber desc
with ta as (select u.id userid,u.realname,cs.snname,cs.sequencenumber from user_info u left join ( select * from check_info) c on c.userid=u.id left join checksn cs on c.checksnid=cs.id where u.schoolid=7 and u.schoolyear=2010 and u.schoolclass=1 ) select top 1 * from ta order by sequencenumber desc
from
(select u.id userid,
u.realname,
cs.snname,
cs.sequencenumber,
row_number() over(partition by u.id order by cs.sequencenumber desc) 'rn'
from user_info u
left join check_info c on c.userid=u.id
left join checksn cs on c.checksnid=cs.id
where u.schoolid=7 and u.schoolyear=2010 and u.schoolclass=1) t
where rn=1
order by sequencenumber desc
(select u.id userid,u.realname,cs.snname,cs.sequencenumber
from user_info u
left join ( select * from check_info) c on c.userid=u.id
left join checksn cs on c.checksnid=cs.id
where u.schoolid=7 and u.schoolyear=2010 and u.schoolclass=1
)
select top 1 * from ta order by sequencenumber desc