RE: PL/SQL stumper

[Lofback, Chris]

I thought the same thing at first, but you cannot sort on a field that is not selected when using DISTINCT.  And adding the field throws off the records selected because DISTINCT looks at the entire row, not just the one column...


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]

---