October 8, 2009 at 11:48 pm
I have following table
Create table scenario (studentID smallint, certID smallint, issuedate datetime)
GO
populated with data as
insert into scenario --values (studentID, certID, issueDate)
select 1,12,'2008-10-13' union all
select 1,13,'2009-06-11' union all
select 2,16,'2009-08-10' union all
select 2,21,'2007-07-23' union all
select 3,23,'2009-11-25'
GO
I am required to get records grouped on studentID with last certificate issued according to issue date.
studentID certID issuedate
1132009-06-11 00:00:00.000
2162009-08-10 00:00:00.000
3232009-11-25 00:00:00.000
Please suggest efficient way for it.
Regards
DBDigger Microsoft Data Platform Consultancy.
October 8, 2009 at 11:59 pm
select a.studentID,a.certID,a.issuedate
from scenario a,
(
select studentID,max(issuedate)issuedate
from scenario
group by studentID
)as b
where a.studentID = b.studentID
and a.issuedate = b.issuedate
order by a.studentID
October 9, 2009 at 6:57 am
How about this?
Select * from (Select *, Row_Number()
OVER (PARTITION BY studentid order by issuedate desc) as rownum
from scenario) urtable where rownum = 1
---------------------------------------------------------------------------------
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply