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   

Lofback, Chris wrote: ----- Excess quoted text cut - see Original Post for more ----- SELECT  * FROM  (   SELECT  provider,     MAX(datestamp) AS lastDate   FROM  table   ORDER BY MAX(datestamp) DESC   ) a WHERE  rownum < 8 Jochem

a Top-N styled SQL...something like SELECT * FROM   (  SELECT *       FROM tablename       OREDER BY datestamp    ) WHERE rownum < 8 Doug 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   _____  

----- Excess quoted text cut - see Original Post for more ----- 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.

oops...left out the distinct provider part.... SELECT * FROM   (  SELECT provider,Max(datestamp)       FROM tablename       ORDER BY Max(datestamp)    ) WHERE rownum < 8 Doug a Top-N styled SQL...something like SELECT * FROM   (  SELECT *       FROM tablename       OREDER BY datestamp    ) WHERE rownum < 8 Doug 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   _____   _____  

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 ----- Excess quoted text cut - see Original Post for more ----- 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.   _____  

This throws an error: ORA-00937: not a single-group group function But it gives me some ideas to work with. Thanks, Chris Lofback, Chris wrote: ----- Excess quoted text cut - see Original Post for more ----- SELECT * FROM ( SELECT provider, MAX(datestamp) AS lastDate FROM table ORDER BY MAX(datestamp) DESC ) a WHERE rownum < 8 Jochem   _____  

Lofback, Chris wrote: > This throws an error: >   > ORA-00937: not a single-group group function Forgot the GROUP BY: SELECT * FROM  (   SELECT  provider,     MAX(datestamp) AS lastDate   FROM table   GROUP BY provider   ORDER BY MAX(datestamp) DESC   ) a WHERE rownum < 8 Jochem

This doesn't quite do it.  The problem is that the recordset from which it gets the first 7 (by rownum) is alphabetical.  So the 7 records are the first 7 of the alphabetically sorted group, not the first 7 by date (which could then be sorted alpha using QofQ).  I don't know if I'm explaining this clearly--but does that make sense? Thanks again, Chris Lofback, Chris wrote: > This throws an error: >   > ORA-00937: not a single-group group function Forgot the GROUP BY: SELECT * FROM ( SELECT provider, MAX(datestamp) AS lastDate FROM table GROUP BY provider ORDER BY MAX(datestamp) DESC ) a WHERE rownum < 8 Jochem   _____  

Lofback, Chris wrote: > This doesn't quite do it.  The problem is that the recordset from which it gets the first 7 (by rownum) is alphabetical. That is weird, they are explicitly sorted. How about: SELECT  provider,   lastDate FROM  (   SELECT  provider,     MAX(datestamp) AS lastDate,     rownum AS rn   FROM  table   GROUP BY provider   ORDER BY MAX(datestamp) DESC   ) a WHERE  a.rn < 8 Which Oracle version are you using? Jochem

Are you sure you are doing the sub-select (SELECT * FROM (SELECT ...) ? I do this all the time and it works perfectly. If you only have one select, you get the result you described. Pascal ----- Excess quoted text cut - see Original Post for more -----

> Which Oracle version are you using? Oracle8i And I am working on providing a better explanation of the query results.  Stay tuned... Thanks, Chris Lofback Web Administrator Ceridian Benefits Services 3201 34th Street S. St. Petersburg, FL 33711 727-395-8881

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

select provider, lastDate from (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) where rownum <<= 7 ORDER BY lastDate desc, provider

SELECT * FROM ( 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!!!!! ----- Excess quoted text cut - see Original Post for more -----

Won't work like intended. You need to put the order by for lastdate in the inner select. ROWNUM is calculated before the order by.   ----- Excess quoted text cut - see Original Post for more -----

>ROWNUM is calculated before the order by.   Good to know. Thanks. Guess it was a fluke that I actually got back what looked like the expected result set. ~Dina

This is as close as I can get, it looks like, but it still doesn't work quite right.  When I dump the records used by this query and sort them in date order, I have a value that is second in the list that does not make the final top 7.  I don't understand why but I'll keep chopping at it. Thanks all, Chris SELECT * FROM ( 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!!!!! ----- Excess quoted text cut - see Original Post for more -----   _____  

It looks like this is as close as I can get but it still doesn't work quite right.  When I dump the records used by this query and sort them in date order, I have a value that is second in the list that does not make the final top 7...  I don't understand why but I'll keep chopping at it. Thanks all, Chris SELECT * FROM ( 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!!!!!

A tip: run the inner SELECT and dump that (if there aren't too many records). The final query should give you the first 7 of this SELECT. If you have several providers with the same date, they may not come in the same order in the inner select as in the sorted table. ----- Excess quoted text cut - see Original Post for more -----

Yes, the final query gives the first seven of the inner SELECT.  It is the inner SELECT that does not appear to be sorting as expected.  I don't understand why a record that is second overall when sorted by date does not make it into the first 7 when using MAX() on the date and grouping by provider... Thanks for your help, Chris A tip: run the inner SELECT and dump that (if there aren't too many records). The final query should give you the first 7 of this SELECT. If you have several providers with the same date, they may not come in the same order in the inner select as in the sorted table. ----- Excess quoted text cut - see Original Post for more -----

Because this is up to the database. You only specify: sort on date, you don't specify how records with the same date are sorted. The database may (and probably will) do that differently if you have a group by than without a group by. If it really needs to be in that order you may have to do 3 (or more) subselects or even write a storedproc Pascal ----- Excess quoted text cut - see Original Post for more -----