How to use Max function int this case

[#1]

Hi,

I have three fields- ID, Date and code each ID has multiple date and respective multiple codes. The way i am trying to do this is i need to create a filter which just says (1,0) where if it's "1" then there is ID with a most recent date and respective code, if "0" default it to the way it is where ID has multiple dates and respective codes.

I have tried to create a different table and concatenate or join back to the actual table no nothing worked for me, hope that some one can help me with this?

ID	Date	Code
12894	1/22/2013	acedr
12894	1/23/2013	teyhcu
12894	1/24/2013	sheyc
12894	1/25/2013	uieldo
19028	2/16/2013	odleif
19028	2/17/2013	iepdls
19028	2/18/2013	qpalif
19028	2/19/2013	oslpar

[swuehl · Accepted solution]

Maybe like

Table1:

LOAD ID, Date, Code FROM ...;

LEFT JOIN LOAD

     ID,

     max(Date) as Date,

     1 as MaxDate

RESIDENT Table1

GROUP BY ID;

Your Date should be recognized as Date by QV, having a numeric representation. Set your DateFormat variable accordingly, if needed.

edit: Selecting 1 from field MaxDate should limit your records to the latest codes then, not selecting anything in field MaxDate should show all records.

[#3]

This definetly makes sense Swuehl, I think this should give me the desired result but right now i am still doing some testing on this. But thanks for the quick response.

[#4]

Exact result that i needed.