Left join to last record

[brulard]

hi,

I need to join my table to the last date record of another table (an ID will have several records, where only the latest one is of interest).

I can do this this way, but it has the inconvenience of needing me to first query every single last record from the other table, prior to my performing the join. Pls let me know if there is a better way of doing this. Thanks,

Proc sql;create table want_preliminary as

Select *

From table_warehouse

Group by  id

Having bill_yr_mth=max(bill_yr_mth);quit;

Proc sql;create table  want_final as

Select b.amtount,b.bill_yr_mth,a.*

From have as a left join want_preliminary as b on

a.id=b.id ;quit;

[Astounding]

I'm sure there are some SQL wizards that could make this happen in a single SELECT statement.  But the main improvement you can make is on the first SELECT.  Instead of SELECT *, bring in only the fields you need:  SELECT id, bill_yr_mth, amtount

Once that is in place, your second SELECT statement can be simplified:

select a.*, b.* from have a, want_preliminary b where a.id=b.id;

There's no need for a left join when you are checking for matching IDs.

[thomp7050]

Right.  To add on to what @Astounding said, you can do it in a single query, but it will not necessarily be more efficient.  Just less real estate on the code file (code not tested.)

Proc sql;create table want_final as
Select b.amtount,b.bill_yr_mth,a.*
From have as a 
inner join (
Select id, amtount, bill_yr_mth
From table_warehouse
Group by  id, amtount, bill_yr_mth
Having bill_yr_mth=max(bill_yr_mth) b
on b.id = a.id;
quit;
  

[brulard]

thanks Astounding.. ? though, there will be instances where there will be no record in table want_preliminary that match id from my original table. If I don't do a left join, will I not miss the records from my original table that got zero match during the join (I would need to keep all IDs/records from my original table)? thank you

[brulard]

thanks Astounding.. ? though, there will be instances where there will be no record in table want_preliminary that match id from my original table. If I don't do a left join, will I not miss the records from my original table that got zero match during the join (I would need to keep all IDs/records from my original table)? thank you

[PGStats]

Same, syntax fixed (I think)

Proc sql;
create table want_final as
Select 
	b.amtount,
	b.bill_yr_mth,
	a.*
From 
	have as a inner join 
	(	Select id, amtount, bill_yr_mth
		From table_warehouse
		Group by  id
		Having bill_yr_mth=max(bill_yr_mth) ) as b
	on b.id = a.id;
quit;