🚀 go-pugleaf

In article <3df8712d@newsgroups.borland.com>, "DP" <daveperkins@_woo-
hoo"-'#-bigfoot.com> says...
> 
> I asked something similar to this in the past, but even with the previous
> answer, this slight variation has me stumped (not hard to do)
> 
> TableA
> A_ID  integer
> Text varchar(100);
> 
> TableB
> B_ID      integer
> A_ID      integer
> Price       integer
> Changed TIMESTAMP
> 
> For every A_ID, I'd like the latest price (deduced by 'Changed') from
> Table_B.  There could be multiple(or zero) entries for A_ID in TableB.
> 
> I need the following columns:  A.A_ID,  A.Text,  B.Price, B.Changed
> 
> where  B.Price, B.Changed are the most recent entry in Table B.

SELECT
  A.A_ID, A.Text, B1.Price, B.Changed
FROM
  TableA A
  LEFT OUTER JOIN TableB B1
    ON (B1.A_ID = A.A_ID)
WHERE
  (B1.A_ID IS NULL) OR
  B1.Changed = (SELECT MAX(B2.Changed) FROM TableB B2
                WHERE (B2.A_ID = A.A_ID));

	This is a corellated subquery.  It runs once for each line in the 
result set.  Hence, if the result set is large your query will be slow.  
You can optimize this with a SELECTable stored proc if this becomes a 
problem.

	HTH,

	-Craig

-- 
Craig Stuntz [TeamB] � Vertex Systems Corp. � Columbus, OH
Delphi/InterBase Weblog : http://delphi.weblogs.com
InterBase Perf. Monitor : http://delphi.weblogs.com/IBPerformanceMonitor
InterBase PLANalyzer 1.1: http://delphi.weblogs.com/IBPLANalyzer