Question -sub query

I’m needing help with a query here. I have a table with an ID and Date which are my PK’s.

I believe this will take a sub query. I want several fields pulled (one per ID). It would be the one with the max(date)

Here is what I have so far (yes I know it doesn’t work but you should get the idea).

select myID, myDate, field1, field2, field3, field4

FROM

(select myID,max(myDate)

from myTable1

WHERE

DATALENGTH(field1)> 0OR

DATALENGTH(field2)> 0OR

DATALENGTH(field3)> 0OR

DATALENGTH(field4)> 0

groupby myID)

By the way the sub query here works on it’s own and gives me the records I want, I just need the other fields pulled in there

Here you are:

SELECT t1.myID, t1.myDateMax, t2.field1, t2.field2, t2.field3, t2.field4FROM

(SELECT myID,max(myDate)AS myDateMax

FROM myTable1WHERE field1ISNOTNULLOR field2ISNOTNULLOR field3ISNOTNULLOR field4ISNOTNULL

GROUPBY myID)AS t1INNERJOIN myTable1AS t2ON t1.myID=t2.myIDAND t1.myDateMax=t2.myDate

|||

Beautiful!!

Thank you Limno

sql