April 9, 2012 at 11:34 am
i have result table which contains result for each student per month
ID StudentID ResultDate Grade
1 1 2011-01-01 00:00:00.000 A
2 1 2011-03-01 00:00:00.000 D
3 1 2011-05-01 00:00:00.000 A
4 1 2011-08-01 00:00:00.000 B
5 1 2011-12-01 00:00:00.000 C
6 2 2011-02-01 00:00:00.000 D
7 2 2011-03-01 00:00:00.000 D
8 2 2011-06-01 00:00:00.000 B
9 2 2011-08-01 00:00:00.000 C
10 2 2011-10-01 00:00:00.000 D
11 3 2011-01-01 00:00:00.000 A
12 3 2011-05-01 00:00:00.000 B
13 3 2011-08-01 00:00:00.000 A
14 3 2011-09-01 00:00:00.000 B
15 3 2011-11-01 00:00:00.000 B
In My situation i shall get that data (The Oldest 3 Result Records for Each User)
1 1 2011-01-01 00:00:00.000 A
2 1 2011-03-01 00:00:00.000 D
3 1 2011-05-01 00:00:00.000 A
6 2 2011-02-01 00:00:00.000 D
7 2 2011-03-01 00:00:00.000 D
8 2 2011-06-01 00:00:00.000 B
11 3 2011-01-01 00:00:00.000 A
12 3 2011-05-01 00:00:00.000 B
13 3 2011-08-01 00:00:00.000 A
Note may be the table records will not be sorted by Result Date
So how can i handle that SQL Statement 🙂 ???
April 9, 2012 at 11:40 am
Can you post the DDL (create table script) and hard coded inserts for us so we can be sure on the data and table structure. if you need help please see the link in my signature.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 9, 2012 at 11:42 am
Here is some code, but it is untested as you didn't provide all the required information to do any testing (no DDL, sample data in a readily consumable format).
with cteStudentData as (
select
row_number() over (partition by StudentID order by ResultDate asc) as rownum,
ID,
StudentID,
ResultDate,
Grade
from
dbo.StudentDataTable)
select
ID,
StudentID,
ResultDate,
Grade
from
cteStudentData
where
rownum <= 3
order by
StudentID,
ResultDate;
April 9, 2012 at 11:48 am
well lynn you beat me to it. had almost the exact same code.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 9, 2012 at 1:44 pm
Thanks a lot Lynn Pettisv 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply