<CFQUERY NAME="whatsnew" DATASOURCE="dpch">
--change this from select * to whatever else
select * from
(
select * FROM whats_new
WHERE dateAdded > #dateAdd("d",-7,Now())#
order by dateAdded DESC
)
WHERE ROWNUM < 4
</CFQUERY>
-----Original Message-----
From: Daniel Kessler [mailto:[EMAIL PROTECTED]
Sent: Thursday, May 06, 2004 12:44 PM
To: CF-Talk
Subject: add a column in oracle
heh actually it confused me a touch. I believe that I understand
what you're saying though I don't know what TOP type records are.
Maybe I should specify my situation. I'm doing a "WHAT'S NEW"
section of a web page. I want the 3 most recently entered items.
I've adjusted my current query to my understanding. Is this right?
<CFQUERY NAME="whatsnew" DATASOURCE="dpch">
(
select * FROM whats_new
WHERE dateAdded > #dateAdd("d",-7,Now())#
order by dateAdded DESC
)
WHERE ROWNUM < 4
</CFQUERY>
I originally had the ROWNUM < 4 along with the other WHERE with an AND.
>Here's a bit of a detailed explanation of Dave means. Something to
>note about using "where rownum < 4" in Oracle is that it doesn't
>pull back the "TOP" type records. It'll simply pull back the first
>3 rows that exist first in the database, and not any specialized
>order you want.
>
>However, there is a fairly simple workaround. Using a subquery will
>give you the "TOP N" effect that MySQL and others. It works like
>this:
>
>Say you wanted to pull back 10 of the highest paid persons at your
>company. Here's the syntax.
>
>--This pulls back the highest ones first, in a "top" type order.
>select name, salary from
>(
> ---this does your ordering for you
> select name, salary from employee_data
> order by salary desc
>)
>where rownum < 11
--
Daniel Kessler
Department of Public and Community Health
University of Maryland
Suite 2387 Valley Drive
College Park, MD 20742-2611
301-405-2545 Phone
www.phi.umd.edu
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
