Jeff Chastain wrote:
>
> Table 1 (key,name) -
> 1,test
> 2,test2
>
> Table 2 (key,thread,timestamp) -
> 1,1,1/1/2003
> 1,2,1/2/2003
> 2,3,1/1/2003
> 1,2,1/4/2003
>
> The desired result set, passing in a key = 1 parameter, would be
>
> key,name,numthreads,timestamp
> 1,test,2,1/4/2004
>
> The difference I am seeing between this and your code is that it appears your code
> counts the number of instances of a given key in table 2 instead of the number of
> distinct threads per key in table 2.
SELECT
t1.key,
t1.name,
COUNT(DISTINCT t2.thread) AS numThreads
MAX(t2.timeStamp) AS timestamp
FROM
t1 INNER JOIN t2 ON t1.key = t2.key
GROUP BY
t1.key,
t1.name
Jochem
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/lists.cfm?link=t:4
Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics.
http://www.fusionauthority.com/signup.cfm
