By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,576 Members | 1,970 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,576 IT Pros & Developers. It's quick & easy.

get last 3 most recent results based on a column

P: 3
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.
Dec 22 '09 #1
Share this Question
Share on Google+
8 Replies


ssnaik84
100+
P: 149
you can get idea from this thread..

Expand|Select|Wrap|Line Numbers
  1. select top 3 * from 
  2. (select * from tablename 
  3. where person = @personid 
  4. order by date desc) as RS
  5.  
now, your new resultset RS will have last 3 results..
you can join it with your original table.
Dec 23 '09 #2

P: 3
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.
Dec 23 '09 #3

ck9663
Expert 2.5K+
P: 2,878
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 :)

Expand|Select|Wrap|Line Numbers
  1.  
  2.  
  3.  
  4. with lastrecord (personid, lastdate) 
  5. as
  6. (
  7.    select personid, max(date) as lastdate 
  8.    from transactiontable group by personid
  9. ), 
  10. last2record (personid, last2date) 
  11. as 
  12. (
  13.    select top 1 t.personid, max(t.date) as last2date 
  14.    from transactiontable t      
  15.       inner join lastrecord l on t.personid = l.personid and t.date < l.lastdate
  16.    group by personid where date    
  17. ),
  18. last3record (personid, last3date) 
  19. as 
  20. (
  21.    select t.personid, max(t.date) last3date 
  22.    from transactiontable t
  23.       inner join last2record l on t.personid = l.personid and t.date < l.last2date
  24.    group by personid where date    
  25. )
  26. select 
  27.    p.personid, l1.lastdate, l2.last2date, l3.last3date
  28. from personstable p
  29.    left join lastrecord l1 on p.personid = l1.personid
  30.    left join last2record l2 on p.personid = l2.personid
  31.    left join last3record l3 on p.personid = l3.personid
  32.  
  33.  
Happy Coding!!!


~CK
Dec 23 '09 #4

nbiswas
100+
P: 149
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.
Dec 24 '09 #5

ssnaik84
100+
P: 149
Well nbiswas.. then use TOP... :)
isn't that simple..??

Expand|Select|Wrap|Line Numbers
  1. select top 3 * from
  2. (select top (select COUNT(*) from tablename) * 
  3. from tablename
  4. where person = @personid
  5. order by date desc) as RS
Dec 24 '09 #6

nbiswas
100+
P: 149
Yes u r right. Now it looks fine.
Dec 24 '09 #7

P: 3
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.
Jan 5 '10 #8

ck9663
Expert 2.5K+
P: 2,878
Sorry about that.

Try this:

Expand|Select|Wrap|Line Numbers
  1.  
  2. with lastrecord (personid, lastdate) 
  3. as
  4. (
  5.    select personid, max(date) as lastdate 
  6.    from transactiontable group by personid
  7. ), 
  8. last2record (personid, last2date) 
  9. as 
  10. (
  11.    select top 1 t.personid, max(t.date) as last2date 
  12.    from transactiontable t      
  13.       inner join lastrecord l on t.personid = l.personid and t.date < l.lastdate
  14.    group by personid 
  15. ),
  16. last3record (personid, last3date) 
  17. as 
  18. (
  19.    select top 1 t.personid, max(t.date) last3date 
  20.    from transactiontable t
  21.       inner join last2record l on t.personid = l.personid and t.date < l.last2date
  22.    group by personid 
  23. )
  24. select 
  25.    p.personid, l1.lastdate, l2.last2date, l3.last3date
  26. from personstable p
  27.    left join lastrecord l1 on p.personid = l1.personid
  28.    left join last2record l2 on p.personid = l2.personid
  29.    left join last3record l3 on p.personid = l3.personid
  30.  
  31.  
Happy Coding !!!

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

--- CK
Jan 7 '10 #9

Post your reply

Sign in to post your reply or Sign up for a free account.