SELECT
d.provider,
MAX(m.date_submitted) AS lastDate
FROM
fsa_detail d,
fsa_master m
WHERE
d.masterid = m.id
AND m.ssn = '123-45-6789'
GROUP BY
d.provider
ORDER BY
MAX(m.date_submitted) DESC )
WHERE ROWNUM <= 7
ORDER BY provider
DON'T use rownum in your inner query!!!!!
> -----Original Message-----
> From: Lofback, Chris [mailto:[EMAIL PROTECTED]
> Sent: woensdag 26 mei 2004 21:13
> To: CF-Talk
> Subject: RE: PL/SQL stumper
>
> All right, I guess I'll need to provide actual database info
> to figure this out. Here's part of Jochem's suggestion, with
> mods to fit the actual schema (and to fix a grouping error):
>
> SELECT
> d.provider,
> MAX(m.date_submitted) AS lastDate,
> rownum AS rn
> FROM
> fsa_detail d,
> fsa_master m
> WHERE
> d.masterid = m.id
> AND m.ssn = '123-45-6789'
> GROUP BY
> d.provider,
> rownum
> ORDER BY
> MAX(m.date_submitted) DESC
>
> The above query returns this recordset:
>
> PROVIDER LASTDATE RN
> --------------------------------------------
> Dr. Milton 25-MAY-04 15
> Dr. Uptagraff 25-MAY-04 13
> Duncan Health Care 25-MAY-04 28
> Eckerd Drugs 25-MAY-04 27
> Hillman's Pharmacy 25-MAY-04 22
> Home Depot 25-MAY-04 10
> Publix 25-MAY-04 24
> Publix Pharmacy 25-MAY-04 14
> Timmons Drugs 25-MAY-04 8
> Xylophone Inc 25-MAY-04 11
> Williams and Assoc. 25-MAY-04 17
> Wellness Center 25-MAY-04 12
> Walmart 25-MAY-04 26
> Walgreens 25-MAY-04 25
> Walgreen's 25-MAY-04 18
> Timmons Drugs 25-MAY-04 16
> Smith's Health Supplies 25-MAY-04 21
> Publix 25-MAY-04 20
> Hush Puppy Shoes 25-MAY-04 23
> Mease Clinic 25-MAY-04 19
> Publix Pharmacy 24-MAY-04 9
> Walmart 24-MAY-04 7
> Treebeard 14-MAY-04 6
> Publix 14-MAY-04 4
> Walmart 14-MAY-04 5
> Publix 14-MAY-04 2
> Treebeard 14-MAY-04 1
> Walmart 14-MAY-04 3
>
> What I need is the first 7 (or any arbitrary number) DISTINCT
> providers from this recordset (ie, the 7 most recent
> providers) in alpha order. All my solutions so far required
> grouping, which put the providers in alpha order first and
> then I pulled the first 7. How can I get the 7 first and
> THEN sort by alpha?
>
> Thanks for your patience and help!
> Chris
>
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
