>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
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
3 (or more)
subselects or even write a storedproc
Pascal
> -Original Message-
> From: Lofback, Chris [mailto:[EMAIL PROTECTED]
> Sent: donderdag 27 mei 2004 16:38
> To: CF-Talk
> Subject: RE: PL/SQL stumper
>
> Yes, the final query gives the first seven of the i
ng by provider...
Thanks for your help,
Chris
-Original Message-
From: Pascal Peters [mailto:[EMAIL PROTECTED]
Sent: Thursday, May 27, 2004 10:27 AM
To: CF-Talk
Subject: RE: PL/SQL stumper
A tip: run the inner SELECT and dump that (if there aren't too many
records). The final query should gi
Message-
> From: Lofback, Chris [mailto:[EMAIL PROTECTED]
> Sent: donderdag 27 mei 2004 15:51
> To: CF-Talk
> Subject: RE: PL/SQL stumper
>
> 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
at it.
Thanks all,
Chris
-Original Message-
From: Pascal Peters [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 26, 2004 5:38 PM
To: CF-Talk
Subject: RE: PL/SQL stumper
SELECT * FROM (
SELECT
d.provider,
MAX(m.date_submitted) AS lastDate
FROM
fsa_detail d,
fsa_master m
WHERE
d.masterid =
chopping at it.
Thanks all,
Chris
-Original Message-
From: Pascal Peters [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 26, 2004 5:38 PM
To: CF-Talk
Subject: RE: PL/SQL stumper
SELECT * FROM (
SELECT
d.provider,
MAX(m.date_submitted) AS lastDate
FROM
fsa_detail d,
fsa_master m
WHERE
d.masterid =
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.
> -Original Message-
> From: Dina Hess [mailto:[EMAIL PROTECTED]
> Sent: donderdag 27 mei 2004 1:24
> To: CF-Talk
> Subject: Re: PL/SQL
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
[Todays Threads]
[This Message]
[Subscription]
[Fast
n 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
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.m
> 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
[Todays Threads]
[This Message]
mei 2004 19:40
> To: CF-Talk
> Subject: RE: PL/SQL stumper
>
> 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 f
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
explaining this clearly--but does that make sense?
Thanks again,
Chris
-Original Message-
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 26, 2004 1:25 PM
To: CF-Talk
Subject: Re: PL/SQL stumper
Lofback, Chris wrote:
> This throws an error:
>
> ORA-0
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
[Todays Threads]
[Th
This throws an error:
ORA-00937: not a single-group group function
But it gives me some ideas to work with.
Thanks,
Chris
-Original Message-
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 26, 2004 12:20 PM
To: CF-Talk
Subject: Re: PL/SQL stumper
Lofback
[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
PL/SQL stumper
a Top-N styled SQL...something like
SELECT *
FROM
( SELECT *
FROM tablename
OREDER BY datestamp
)
WHERE rownum < 8
Doug
-Original Message-
From: Lofback, Chris [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 26, 2004 12:09 PM
To: CF-Talk
Subject:
Lofback, Chris wrote:
> I need to get the first 7 distinct providers in date order with newest first. Can it be done without "getting fancy"?
> 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
>
>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
a Top-N styled SQL...something like
SELECT *
FROM
( SELECT *
FROM tablename
OREDER BY datestamp
)
WHERE rownum < 8
Doug
-Original Message-
From: Lofback, Chris [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 26, 2004 12:09 PM
To: CF-Talk
Subject: 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 provid
22 matches
Mail list logo
