GOT IT!
THANKS TO MIKE KLOSTERMEYER FROM CFWEBTOOLS
select v.itemid, v.title, e.datecreated, e.eventid from v_items v
INNER Join Events e
on v.itemid = e.itemid
where
e.eventid in (
select top 1 e2.eventid
from events e2
where e2.itemid = v.itemid
AND e.createdbyuserid = 92
order by e2.datecreated desc
)
order by e.datecreated desc
-----Original Message-----
From: Teddy Payne [mailto:[EMAIL PROTECTED]
Sent: Thursday, November 16, 2006 9:48 AM
To: CF-Talk
Subject: Re: CALLING ALL SQL EXPERTS, Please help
Can you provide a sample of the table prior to your joins so that I may see
your foreign keys.
Teddy
On 11/16/06, Axel Jensen <[EMAIL PROTECTED]> wrote:
>
>
>
>
>
>
> I'm trying to create a "most recently updated" list of tracks that we
> update, and track our bugs, and feature requests with. The list that I
> want
> will end up looking like this. (where the query ends up just grabbing the
> most recent results, and doesn't duplicate.
>
>
>
> 92 1028 Northern Natural Gas Flash Quiz Review 2006-11-15
> 15:46:53.280
>
> 92 975 New E-commerce Integration 2006-11-10 17:19:59.450
>
> 92 974 Feedback Form 2006-11-10 17:12:29.703
>
> 92 1044 SQL Changes and Calander Move 2006-11-08 08:30:46.357
>
> 92 897 web based problem log for nphl.org and reglab.org
> 2006-11-06
> 16:42:19.153
>
>
>
>
>
>
>
> I realize that the reason I'm getting the duplicates is because I'm trying
> to get distinct values on the date created. therefor I'm getting all the
> correct values. as far as sql goes.
>
>
>
> But I'm not sure how I would go about getting what I need. I know that if
> I
> cut the date out of the select statement, the query works, but then I
> can't
> sort by the most recently updated. so the list becomes rather useless to
> us.
> any help would be great
>
>
>
>
>
>
>
> SELECT DISTINCT e.createdByUserID,i.itemID, i.title, e.datecreated
>
> FROM events e
>
> JOIN v_items i ON e.itemID = i.itemID
>
> WHERE e.createdbyuserid = 92
>
> ORDER BY e.datecreated desc
>
>
>
> Results:
>
> 92 1028 Northern Natural Gas Flash Quiz Review 2006-11-15
> 15:46:53.280
>
> 92 975 New E-commerce Integration 2006-11-10 17:19:59.450
>
> 92 974 Feedback Form 2006-11-10 17:12:29.703
>
> 92 1045 Email latest cow report to new cow rpt
> subscribers 2006-11-09
> 15:03:38.140
>
> 92 1028 Northern Natural Gas Flash Quiz Review 2006-11-09
> 10:39:28.483
>
> 92 1028 Northern Natural Gas Flash Quiz Review 2006-11-09
> 08:31:58.840
>
> 92 1028 Northern Natural Gas Flash Quiz Review 2006-11-08
> 16:30:32.343
>
> 92 1028 Northern Natural Gas Flash Quiz Review 2006-11-08
> 11:51:49.967
>
> 92 975 New E-commerce Integration 2006-11-08 10:55:34.903
>
> 92 1044 SQL Changes and Calander Move 2006-11-08 08:30:46.357
>
> 92 1044 SQL Changes and Calander Move 2006-11-07 16:34:47.810
>
> 92 975 New E-commerce Integration 2006-11-06 16:44:33.983
>
> 92 897 web based problem log for nphl.org and reglab.org
> 2006-11-06
> 16:42:19.153
>
> 92 1028 Northern Natural Gas Flash Quiz Review 2006-11-06
> 16:40:46.700
>
> 92 1028 Northern Natural Gas Flash Quiz Review 2006-11-06
> 11:14:32.670
>
> 92 1028 Northern Natural Gas Flash Quiz Review 2006-11-06
> 11:06:30.793
>
>
>
>
>
> Axel
>
>
>
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four
times a year.
http://www.fusionauthority.com/quarterly
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:260675
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
