Max Date

[LAM986]

Hello,
My data looks like:

StudentID Review1 Review2
228017 12/30/2008 NULL
228017 3/12/2009 NULL

I want it to look like:
StudentID Review1 Review2
228017 12/30/2008 3/12/2009

I'm using a simple
SELECT StudentID
, max(Review1)
, max(Review2)
FROM WBLP

Thank you

 

[markros]

select S.StudentID, Min(Review1) as Review1, Max(Review1) as Review2 from WBLP

would this work?

 

[LAM986]

I tried that and I get

StudentID Review1 Review2
228017 12/30/2008 NULL

Thanks for responding

 

[bborissov]

nope, you didn't tried it right

select S.StudentID, 
       Min(Review1) as Review1, 
       Max([COLOR=red][b]Review1[/b][/color]) as Review2
FROM WBLP

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

Based on the data you posted you should have gotten the correct result (not NULL) using the query I provided.

 

[LAM986]

That worked! Thank you so much.

 

[markros]

)) Did it work because the last portion was in red? ))))

 

[markros]

Actually, there was a typo in the query - there should not be S.StudentID - should be just StudentID (also I think I meant to add GROUP BY StudentID)

 

[LAM986]

Makros, I'm sorry. Yours would have worked, I didn't see Review1 on the second field. I apologize.

 

[LAM986]

Thank you guys for your help.

 

[markros]

No problem, there were several typos in the query anyway.

 

[LAM986]

One more question. How do I handle if Review1 is NULL

So the data looks like:

StudentID Review1 Review2
228017 12/30/2008 NULL
228017 NULL NULL

The result I get in this case is below, when I would now want to put NULL in review2

StudentID Review1 Review2
228017 12/30/2008 12/30/2008

 

[markros]

Try

select S.StudentID,        Min(Review1) as Review1,        case when min(Review1) = Max(Review1) then NULL else Max(Review1) end as Review2 FROM WBLP

 

[LAM986]

Thanks again guys. I knew I had to do a Case statement, but couldn't get the right outcome.