I could do both <= and >= at the end like Deanna (it is just the >= that
fails in the 2nd query). I do the <= in the 2nd because the result set
returned from that query is likely to be smaller than if I do it in the
3rd query. The end result is exactly the same
You need 3 queries because ROWNUM is
Pascal that works great. I'm still trying to understand it a little better.
Thanks for the help!
~|
Find out how CFTicket can increase your company's customer support
efficiency by 100%
http://www.houseoffusion.com/banners/view
>SELECT title
>FROM bep_resource
>WHERE 1 = 1
>ORDER BY UPPER(title)
>Does it look right?
yep.
>SELECT t.*, ROWNUM AS RN
>FROM (
...
>ORDER BY UPPER(title)
>) t
>
>Still good?
yep. This is the step that Pascal suggsted and the one that makes a difference.
>Next Step:
>SELECT * FROM (
>SELECT t
>There could be a number of reasons why it isn't showing. Maybe debug
>isn't activated for your IP. Or maybe it is blocked at the app level. Or
>maybe database activity isn't checked.
I suspect it's our webhosting services' preference.
~
Okay, let's take a step back. (Examples from a table in my db that works.)
Why don't you query for the whole list of agencies, using the "order by
upper(agency)" clause. This will get you the list in alphabetical order.
SELECT title
FROM bep_resource
WHERE 1 = 1
ORDER BY UPPER(title)
Dump it in
t: 28 January 2005 12:28
> To: CF-Talk
> Subject: Re: ORDER BY on a query within a query
>
> >Can you show us what Oracle is getting? the query from the debug
output?
>
> I put debug="yes" in the cfquery line, but I didn'
>Can you show us what Oracle is getting? the query from the debug output?
I put debug="yes" in the cfquery line, but I didn't see a change in the
display. I expected some sql output.
~|
Logware (www.logware.us): a new and con
)
) t
WHERE ROWNUM <=
)
WHERE RN >=
Pascal Peters
> -Original Message-
> From: Daniel Kessler [mailto:[EMAIL PROTECTED]
> Sent: 27 January 2005 19:30
> To: CF-Talk
> Subject: ORDER BY on a query within a query
>
> I have a query in a query that grabs al
>It doesn't make any difference whether The Order By is in the
>inner or outer select. The inner select is really a view, so you
>can order the view, or the select statement running against the
>view. Same results. The results are weird though.
It is odd. It seems to be ordering the pages alphabe
>Are you testing this query in oracle, or just from your cf output? Also, are
>you expecting that Oracle will order the numbers in the varchar2 field as
>numbers? It won't happen. Varchar2 fields are ordered by ascii or unicode
>character number, I believe, which is why they're case sensitive.
It doesn't make any difference whether The Order By is in the
inner or outer select. The inner select is really a view, so you
can order the view, or the select statement running against the
view. Same results. The results are weird though.
>Can you show us what Oracle is getting? the query from
Can you show us what Oracle is getting? the query from the debug output?
On Thu, 27 Jan 2005 14:27:14 -0400, daniel kessler <[EMAIL PROTECTED]> wrote:
> >I can't speak for Oracle specifically, but in general, the outer select
> >is not required to return the results in the same order as the
> >s
tive.
Try running just the inner query in an oracle client. Get the results you
expect? No? Then, somethings going on there.
- Original Message -
From: "daniel kessler" <[EMAIL PROTECTED]>
To: "CF-Talk"
Sent: Thursday, January 27, 2005 12:27 PM
Subject: Re
>I can't speak for Oracle specifically, but in general, the outer select
>is not required to return the results in the same order as the
>subselect. Try moving the ORDER BY to the outer select and see if that
>works. I'm far from sure this is your problem, but it's certainly
>something to try
I can't speak for Oracle specifically, but in general, the outer select
is not required to return the results in the same order as the
subselect. Try moving the ORDER BY to the outer select and see if that
works. I'm far from sure this is your problem, but it's certainly
something to try.
--
I have a query in a query that grabs all the hits and then returns
the first 10 (thanks again folks!). It does an ORDER BY on agency
which is an Oracle varchar2(100). The ORDER BY is in the first query
and the second query just grabs 10 at a time. Largely it does this
correctly, but I've not
16 matches
Mail list logo
