sql self join on previous record for audit change -
i trying sql query return rows status of previous time there audit log same account like create table aud ( id int identity(1,1), date_time datetime, [user] varchar(20), account varchar(20), [status] char(2) ) insert aud values(getdate(),'guy','123456','00') insert aud values(getdate(),'guy','123456','01') insert aud values(getdate(),'guy','123456','02') insert aud values(getdate(),'guy','123456','00') insert aud values(getdate(),'guy','123456','04') insert aud values(getdate(),'guy','123456','01') what looking result lists previous account status date_time, user, account, status, previous_status i tried select a.*, b.* aud join aud b on a.account = ( select top 1 account aud a.account = b.account , a.id > b.id order date_time asc ) but joins more previous one if using sql server 2012+, can use lag() single colu...