Thanks,
Chris
-----Original Message-----
From: Dave Carabetta [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 26, 2004 12:20 PM
To: CF-Talk
Subject: RE: PL/SQL stumper
>I have a table like the following. With just CFML (v5) and plain Oracle
>PL/SQL--ie, no temp tables or stored procedures--is there a way to get,
>say, the 7 most recent distinct providers? I've tried every which way,
>even using QofQ with MAXROWS=7, but that only returned the first 7 distinct
>providers alphabetically. I need to get the first 7 distinct providers in
>date order with newest first. Can it be done without "getting fancy"?
>
>Thanks,
>Chris
>
>PROVIDER DATESTAMP
>Eckerd Drugs 25-MAY-04
>Walmart 25-MAY-04
>Walgreens 23-MAY-04
>Publix 23-MAY-04
>Hush Puppy Shoes 23-MAY-04
>Publix 22-MAY-04
>Walgreen's 20-MAY-04
>Timmons Drugs 20-MAY-04
>Publix Pharmacy 19-MAY-04
>Walmart 16-MAY-04
>Publix Pharmacy 16-MAY-04
>Publix 14-MAY-04
>Walmart 14-MAY-04
>Publix 14-MAY-04
>Walmart 14-MAY-04
>
Off the top of my head, but does this work?:
SELECT provider
FROM (
SELECT DISTINCT provider
FROM provider_table
ORDER BY datestamp DESC
)
WHERE rownum <= 7
Regards,
Dave.
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]