House of Fusion
Search over 2,500 ColdFusion resources here
  
Home of the ColdFusion Community

Mailing Lists 		Home /  Groups /  ColdFusion Talk (CF-Talk)

PL/SQL stumper

  << Previous Post |  RSS |  Tree View |  Sort Oldest First |  Subscribe to this Group Next >> 

PL/SQL stumper

>ROWNUM is calculated before the order by.   Dina Hess 05/27/2004 02:18 PM
Because this is up to the database. You only specify: sort on date, you Pascal Peters 05/27/2004 10:53 AM
Yes, the final query gives the first seven of the inner SELECT.  It is Lofback, Chris 05/27/2004 10:40 AM
A tip: run the inner SELECT and dump that (if there aren't too many Pascal Peters 05/27/2004 10:29 AM
It looks like this is as close as I can get but it still doesn't work quite Lofback, Chris 05/27/2004 09:53 AM
This is as close as I can get, it looks like, but it still doesn't work quite Lofback, Chris 05/27/2004 09:49 AM
Won't work like intended. You need to put the order by for lastdate in Pascal Peters 05/27/2004 03:50 AM
select provider, lastDate Dina Hess 05/26/2004 07:29 PM
SELECT * FROM ( Pascal Peters 05/26/2004 05:40 PM
All right, I guess I'll need to provide actual database info to figure this out. Lofback, Chris 05/26/2004 03:39 PM
> Which Oracle version are you using? Lofback, Chris 05/26/2004 03:20 PM
Are you sure you are doing the sub-select (SELECT * FROM (SELECT ...) ? Pascal Peters 05/26/2004 02:39 PM
Lofback, Chris wrote: Jochem van Dieten 05/26/2004 02:25 PM
This doesn't quite do it.  The problem is that the recordset from Lofback, Chris 05/26/2004 02:08 PM
Lofback, Chris wrote: Jochem van Dieten 05/26/2004 01:27 PM
This throws an error: Lofback, Chris 05/26/2004 01:24 PM
I thought the same thing at first, but you cannot sort on a field that is not Lofback, Chris 05/26/2004 01:19 PM
oops...left out the distinct provider part.... Douglas.Knudsen 05/26/2004 12:26 PM
Lofback, Chris wrote: Jochem van Dieten 05/26/2004 12:22 PM
----- Excess quoted text cut - see Original Post for more ----- Dave Carabetta 05/26/2004 12:22 PM
a Top-N styled SQL...something like Douglas.Knudsen 05/26/2004 12:19 PM
I have a table like the following.  With just CFML (v5) and plain Lofback, Chris 05/26/2004 12:12 PM

05/27/2004 02:18 PM
Author: Dina Hess Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32792#164678 >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
05/27/2004 10:53 AM
Author: Pascal Peters Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32792#164635 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 -----
05/27/2004 10:40 AM
Author: Lofback, Chris Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32792#164632 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 -----
05/27/2004 10:29 AM
Author: Pascal Peters Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32792#164631 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 -----
05/27/2004 09:53 AM
Author: Lofback, Chris Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32792#164619 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!!!!!
05/27/2004 09:49 AM
Author: Lofback, Chris Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32792#164616 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 -----   _____  
05/27/2004 03:50 AM
Author: Pascal Peters Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32792#164598 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 -----
05/26/2004 07:29 PM
Author: Dina Hess Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32792#164581 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
05/26/2004 05:40 PM
Author: Pascal Peters Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32792#164575 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 -----
05/26/2004 03:39 PM
Author: Lofback, Chris Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32792#164546 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
05/26/2004 03:20 PM
Author: Lofback, Chris Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32792#164533 > 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
05/26/2004 02:39 PM
Author: Pascal Peters Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32792#164515 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 -----
05/26/2004 02:25 PM
Author: Jochem van Dieten Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32792#164512 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
05/26/2004 02:08 PM
Author: Lofback, Chris Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32792#164496 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   _____  
05/26/2004 01:27 PM
Author: Jochem van Dieten Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32792#164484 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
05/26/2004 01:24 PM
Author: Lofback, Chris Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32792#164483 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   _____  
05/26/2004 01:19 PM
Author: Lofback, Chris Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32792#164481 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.   _____  
05/26/2004 12:26 PM
Author: Douglas.Knudsen Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32792#164469 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   _____   _____  
05/26/2004 12:22 PM
Author: Jochem van Dieten Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32792#164468 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
05/26/2004 12:22 PM
Author: Dave Carabetta Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32792#164467 ----- 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.
05/26/2004 12:19 PM
Author: Douglas.Knudsen Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32792#164465 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   _____  
05/26/2004 12:12 PM
Author: Lofback, Chris Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32792#164462 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   
<< Previous Thread Today's Threads Next Thread >>

Search cf-talk

May 24, 2012

<<   <   Today   >   >>
Su Mo Tu We Th Fr Sa
     1 2 3 4 5
6 7 8 9 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29 30 31     

Designer, Developer and mobile workflow conference

Latest Fusion Authority Articles

Designer, Developer and mobile workflow conference

Want a number like 800-ColdFusion?

My Toll Free 800 Number provides a vanity phone number service to clients who want more than just digits. How would you like 800-coldfusion?

www.MyTollFree800Number.com