May 31, 2013 at 7:37 am
I have a table with studentId, decision and Finishdate
Where studentid is the primary key and there could be none decision earlier and the aacept/deny later or vice-versa for a student.
So, the decision could be accept/deny or None when there is no decision.
I have to select the row of data when there is a decision (that is where decision is other than ’None’ )and finish date is most recent for a studentid.
How do I do that?
Thanks,
blyzzard
May 31, 2013 at 8:56 am
amar_kaur16 (5/31/2013)
I have a table with studentId, decision and FinishdateWhere studentid is the primary key and there could be none decision earlier and the aacept/deny later or vice-versa for a student.
So, the decision could be accept/deny or None when there is no decision.
I have to select the row of data when there is a decision (that is where decision is other than ’None’ )and finish date is most recent for a studentid.
How do I do that?
Thanks,
blyzzard
In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 31, 2013 at 9:02 am
Total shot in the dark:
select
mt.studentId,
max(mt.Finishdate) as MaxDate
from
dbo.myTable mt
where
exists(select 1 from dbo.myTable mt1 where mt.studentid = mt1.studentid and mt1.Decision <> 'None')
group by
mt.studentid;
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply