get last 3 most recent results based on a column

[twomcfly]

Hi

I have a SQL Server 2005 database with a list of results for a variety of people over time. i would like to be able to look at any row and from that row know the last 3 results for that person

the columns are like this

date ----- person ----- result

i would like to be able to add in columns which would show

----- last result ----- last but one result ----- last but two result

i could then look at averages etc so my table (or new table) would be

date ----- person ----- result ----- last result ----- last but one result ----- last but two result

Can anyone help with a way to do this? it would need to find the previous record of the person and get their result and then go to the record of that person before that and get the result etc etc.

thanks in advance and merry christmas.

[ssnaik84]

you can get idea from this thread..

select top 3 * from 
(select * from tablename 
where person = @personid 
order by date desc) as RS
 

now, your new resultset RS will have last 3 results..
you can join it with your original table.

[twomcfly]

thanks for the reply but i was not clear enough in my explanation. the person column of the table has thousands of rows with hundreds of different people listsed. I need to find the last results for each and every row in the table. Obviously this won't be possible for some of the early rows in the table as there will not be previous results.

i hope that explanation makes my case clearer.

[ck9663]

While there's no reply yet, try this one for now...I'm sure there's a better way of doing it, but I got called at work :)

 
 
 
with lastrecord (personid, lastdate) 
as
(
   select personid, max(date) as lastdate 
   from transactiontable group by personid
), 
last2record (personid, last2date) 
as 
(
   select top 1 t.personid, max(t.date) as last2date 
   from transactiontable t      
      inner join lastrecord l on t.personid = l.personid and t.date < l.lastdate
   group by personid where date    
),
last3record (personid, last3date) 
as 
(
   select t.personid, max(t.date) last3date 
   from transactiontable t
      inner join last2record l on t.personid = l.personid and t.date < l.last2date
   group by personid where date    
)
select 
   p.personid, l1.lastdate, l2.last2date, l3.last3date
from personstable p
   left join lastrecord l1 on p.personid = l1.personid
   left join last2record l2 on p.personid = l2.personid
   left join last3record l3 on p.personid = l3.personid
 
 

Happy Coding!!!


~CK

[nbiswas]

Hi ssnaik84,
You cannot use Order by clause inside a subquery otherwise you will encounter the error

Msg 1033, Level 15, State 1, Line 16
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

[ssnaik84]

Well nbiswas.. then use TOP... :)
isn't that simple..??

select top 3 * from
(select top (select COUNT(*) from tablename) * 
from tablename
where person = @personid
order by date desc) as RS

[nbiswas]

Yes u r right. Now it looks fine.

[twomcfly]

only just got back to this what with christmas and all that.

thanks for all the replies. ck9663, I can't seem to get what you have done to work. i get an error saying incorrect syntax near the keyword where on line 13 which is

group by personid where date

any ideas?

also is it right that in just one of the lastrecord sub queries you use select top 1? apologies if i am way off the mark here and thanks for what you have done so far.

[ck9663]

Sorry about that.

Try this:

 
with lastrecord (personid, lastdate) 
as
(
   select personid, max(date) as lastdate 
   from transactiontable group by personid
), 
last2record (personid, last2date) 
as 
(
   select top 1 t.personid, max(t.date) as last2date 
   from transactiontable t      
      inner join lastrecord l on t.personid = l.personid and t.date < l.lastdate
   group by personid 
),
last3record (personid, last3date) 
as 
(
   select top 1 t.personid, max(t.date) last3date 
   from transactiontable t
      inner join last2record l on t.personid = l.personid and t.date < l.last2date
   group by personid 
)
select 
   p.personid, l1.lastdate, l2.last2date, l3.last3date
from personstable p
   left join lastrecord l1 on p.personid = l1.personid
   left join last2record l2 on p.personid = l2.personid
   left join last3record l3 on p.personid = l3.personid
 
 

Happy Coding !!!

The TOP 1 is to only get he first record...

--- CK