Daniel
Due to how and wen rownum is defined, I believe you have to do it like
this in oracle.
SELECT * FROM (
SELECT t.*, ROWNUM AS RN
FROM (
SELECT ...
FROM ...
WHERE 1 = 1 ...
ORDER BY #UPPER(article)
) t
WHERE ROWNUM <= <cfqueryparam value="#url.end_num#"
cfsqltype="cf_sql_integer">
)
WHERE RN >= <cfqueryparam value="#url.begin_num#"
cfsqltype="cf_sql_integer">
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 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 noticed some oddities with the ordering. First,
> it seems to order caps and lowercase separately like a1, a2, A1, A2
> and it seems order them within the 10 instead of within the whole (I
> think).
> Also, if you go here:
> http://hhp.umd.edu/studentservices/internships.cfm and click search
> for a blank search, it provides a search list with agencies 1-9 begin
> with an "a" while agency 10 begins with a "d". Click on page 2 and
> agencies 11-20 begin with an "a". I added an UPPER to the inner
> query ORDER BY to try and fix it, but it didn't. Seems right to me
> though.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Discover CFTicket - The leading ColdFusion Help Desk and Trouble
Ticket application
http://www.houseoffusion.com/banners/view.cfm?bannerid=48
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:192071
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
