Visual Query Builder Get oldest Date

[cowannbell]

I would like if possible, in the query builder, to automatically pull the newest date.

Is that possible and how would I write that criteria?

[gautiergodard]

hey @cowannbell 

Would you be able to share some sample data? 

You would likely want to want to do something in SQL to achieve this.

[gautiergodard]

I am thinking something like what is described in the attached is what you are looking for:

how do I query sql for a latest record date for each user - Intellipaat Community

[cowannbell]

Thank you for that.

I've tried it and it works kind of but I only want to group by the date and I get an error when I try that.  If I group by the other fields, then I get the latest date for the group.  What I want is to return rows that only have the latest date.  I hope that makes sense.

Here is SQL STATEMENT 

select Max(MEMBERSHIP.EFF_DT),
	MEMBERSHIP.GROUP_NBR,
	MEMBERSHIP.PBUSGR_CAP_OFF_ID,
	MEMBERSHIP.PLAN_DESIGN_CD,
	MEMBERSHIP.PRODUCT_LN_CD 
from MEMBERSHIP 
where MEMBERSHIP.PRODUCT_LN_CD in ('01', '02') 
group by MEMBERSHIP.EFF_DT

[KavyaSri9]

Adding Order by  after Group by should work, remove the Max aggregate function.

[cowannbell]

If I remove the max, then it's going to pull everything. I only want the query to look at the date field and pull be only the rows where the date field is the most current but I also need it to pull the other fields.  I just don't want it to group by the other fields.

[gautiergodard]

@cowannbell what you are missing in the statement is the inner join:

select t.username, t.date, t.value

from MyTable t

inner join (

    select username, max(date) as MaxDate

    from MyTable

    group by username

) tm on t.username = tm.username and t.date = tm.MaxDate

In the example provided in the link, the inner join serves at the filter. You can select the max date in the inner join and join on the date field for your larger data set. Some like this:

select t.MEMBERSHIP.EFF_DT,
	t.MEMBERSHIP.GROUP_NBR,
	t.MEMBERSHIP.PBUSGR_CAP_OFF_ID,
	t.MEMBERSHIP.PLAN_DESIGN_CD,
	t.MEMBERSHIP.PRODUCT_LN_CD 
from MEMBERSHIP t
inner join ( 
	select max(MEMBERSHIP.EFF_DT) as Max Date from MEMBERSHIP)
	on t.MEMBERSHIP.EFF_DT=MEMBERSHIP.EFF_DT
where MEMBERSHIP.PRODUCT_LN_CD in ('01', '02') 
group by MEMBERSHIP.EFF_DT

Hope that makes sense.

[cowannbell]

Thank you for your reply.

I'm trying this and I get this error.

[gautiergodard]

Try removing the GROUP BY at the end.

I realize that the below wasnt properly indexed. If you keep the group by, update the field from MEMBERSHIP_EFF_DT to t.MEMBERSHIP_EFF_DT

group by MEMBERSHIP.EFF_DT

[cowannbell · Accepted solution]

I did try this but that still didn't work.  I did find a solution for what I needed.  I just used a summary tool and the Max action on the eff date field and then used a join field to join the data with the max date and got what I needed.

Thank you all so much for your assistance.