I don't remember the syntax for the Sub query but something like this may
work:
SELECT TOP(date_due) as TOP_date_due, name, date_modified
FROM Table
WHERE ID IN ('SELECT DISTINCT name
FROM Table
')
GROUP BY name, date_modified, date_due
ORDER BY date_modified , name, date_modified, date_due
On Tue, Nov 18, 2008 at 5:26 PM, Sung Woo <[EMAIL PROTECTED]> wrote:
> Hi Rob,
>
> That would work in this instance, but I need something a little more
> dynamic. What if the dataset looked like this?
>
> id name date_due date_modified
> 1 Woo 1/1/2009 11/18/2008 4:55PM
> 1 Woo 2/1/2009 11/18/2008 5:21PM
> 1 Woo 3/1/2009 11/18/2008 5:30PM
> 2 Smith 1/1/2009 11/18/2008 5:02PM
> 2 Smith 2/1/2009 11/18/2008 5:03PM
> 2 Smith 3/1/2009 11/18/2008 5:04PM
>
> Then it would bring up two records for Woo but none for Smith. I need to
> bring up the latest record for Woo and the latest record for Smith. And
> keep in mind that this dataset could have a hundred users listed, in which
> case I'd need the latest record for each of the 100 users.
>
> >select top 2 * from tables order by date asc
> >should get you there.
> >
> >Rob
> >
> >
> >
> >>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:315481
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
