Daniel,
It is possible within the same query to return the count of rows
within that same query..
SELECT *
FROM
( SELECT d.*, ROWNUM r, COUNT(docid) OVER() AS rowcount
FROM DOCUMENTS d
ORDER BY title
) WHERE r > 1 AND r < 10
Using count() over () as an analytic function instead of as an
agg
>> Does making sure that it's a varchar do the same thing? IOW, is
>> it making sure that it's not some sort of run statement?
>
>Yes, it simply tells the database server that the value is just that - a
>value rather than an SQL command.
ah great, thanks for the clarity. I have alot of retro-fit
Hey Greg, that worked great!
Now, I have to figure out how to get a count of the inner query because that's
the total number of hits. I tried adding Count, but don't know enough to do it
correctly. Oh well, I'll attack that today before I start another thread.
I appreciate the help everyone.
> OK, I read that and it makes sense. Unfortunately, the
> example of making sure a number is a number, instead of a
> cmd-line string seems a bit obvious.
Most examples are!
> Does making sure that it's a varchar do the same thing? IOW, is
> it making sure that it's not some sort of run stat
Doug that will work for the initial query but he's wanting page
progression... to display rownums between 1 and 25 for example..
That example wont work in this case..
SELECT *
FROM
( SELECT *
FROM DOCUMENTS
ORDER BY title
) WHERE ROWNUM > 20 AND ROWNUM < 30
yields no results..
Howeve
for a TOP N SQL in Oracle...
SELECT *
FROM
( SELECT *
FROM tablename
WHERE foo = goo
ORDER BY columnname
) WHERE rownum <= N
Note where the order by appears, its important to sort the results
first, then nab the top N.
Doug
On Thu, 20 Jan 2005 07:31:31 -0600, Greg Morphis <[EMAIL
Well good, then use what I wrote..
This is for Oracle.
SELECT *
FROM
(
SELECT ROWNUM rID, d.* FROM DOCUMENTS d
ORDER BY docid
) b
WHERE b.rID BETWEEN 2 AND 8
On Thu, 20 Jan 2005 08:26:19 -0400, daniel kessler <[EMAIL PROTECTED]> wrote:
> When I try to run this SQL, I receive th
>Then use what we wrote to build the query, as far as the where
>clause.. use statements..
dang, rewrite time. . . I understand thanks.
>select * from table
>where 1 = 1
>
>and id = cfsqltype="cf_sql_varchar">
>
~|
Logware
When I try to run this SQL, I receive the error
FROM keyword not found where expected
SELECT TOP 25 t1.* FROM (SELECT TOP 50
service_population,agency,city,state,salary_status,int_agency_id FROM
internships) t1
If I remove the TOP 25 and TOP 50 it runs fine, but of course it doesn't do
what I n
Then use what we wrote to build the query, as far as the where
clause.. use statements..
select * from table
where 1 = 1
and id =
On Thu, 20 Jan 2005 07:49:22 -0400, daniel kessler <[EMAIL PROTECTED]> wrote:
> >Is there a chance that anyone of these rows will be deleted? If so
> >
>Is there a chance that anyone of these rows will be deleted? If so
>then you wont want to do a query where id between m and n
>Say you delete id 250... then run a query for 201-300 expecting to
>return 100 results, you wont.. you'll return 100 minus the people you
>deleted.. could mess up your out
>> I've not used cfqueryparam before. Can you clarify how I can
>> implement this so that it makes my WHERE more secure? I've
>> never really understood cfqueryparam.
>
>You can read all about it here:
>http://www.macromedia.com/devnet/mx/coldfusion/articles/cfqueryparam.html
OK, I read that a
http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/tags-b20.htm#wp1102474
Dave Watts <[EMAIL PROTECTED]> wrote:
> I've not used cfqueryparam before. Can you clarify how I can
> implement this so that it makes my WHERE more secure? I've
> never really understood cfqueryparam.
You can r
Is there a chance that anyone of these rows will be deleted? If so
then you wont want to do a query where id between m and n
Say you delete id 250... then run a query for 201-300 expecting to
return 100 results, you wont.. you'll return 100 minus the people you
deleted.. could mess up your output.
> I've not used cfqueryparam before. Can you clarify how I can
> implement this so that it makes my WHERE more secure? I've
> never really understood cfqueryparam.
You can read all about it here:
http://www.macromedia.com/devnet/mx/coldfusion/articles/cfqueryparam.html
Dave Watts, CTO, Fig Le
>I just posted this in response to a different question. A note on your
>use of #preservesinglequotes# here - that is inherently unsafe and
>should really be replaced by a cfqueryparam. As it stands your find
>text could allow a malicious SQL injection to delete tables or pull back
>login nam
> well that's what I'm trying to avoid, though I thought that
> with startRow and maxrow, it limited the search to only grab
> those 25. You're saying it grabs all of them and only shows
> me the 25 in the delivered recordSet?
The STARTROW and MAXROWS attributes of the CFOUTPUT tag have no eff
Yeah, for larger results you'd want to limit the results within the
query, not the output..
IE. (oracle)
SELECT *
FROM
(
SELECT ROWNUM ID, d.* FROM DOCUMENTS d
ORDER BY docid
)
WHERE ID BETWEEN 25 AND 49
On Wed, 19 Jan 2005 11:33:22 -0400, daniel kessler <[EMAIL PROTECTED]> wrot
>That isn't really a good idea since you are asking for all your records
>into CF before you manipulate the results. The result is an app which
>doesn't scale well and chokes on simple paging query.
well that's what I'm trying to avoid, though I thought that with startRow and
maxrow, it limite
That isn't really a good idea since you are asking for all your records
into CF before you manipulate the results. The result is an app which
doesn't scale well and chokes on simple paging query.
Greg Morphis wrote:
>Do your query and then loop over it.. using cfloop with startrow and
>maxrow
I just posted this in response to a different question. A note on your
use of #preservesinglequotes# here - that is inherently unsafe and
should really be replaced by a cfqueryparam. As it stands your find
text could allow a malicious SQL injection to delete tables or pull back
login names an
Do your query and then loop over it.. using cfloop with startrow and
maxrow attributes
On Tue, 18 Jan 2005 14:19:34 -0500, Daniel Kessler <[EMAIL PROTECTED]> wrote:
> I have a search (http://hhp.umd.edu/studentservices/internships.cfm).
> If I have alot of hits in the search results, want to disp
I have a search (http://hhp.umd.edu/studentservices/internships.cfm).
If I have alot of hits in the search results, want to display the
first 25 and then do page 1,2,n after that. Is the idea that I just
search for the first 25 with maxrows=25 and then follow a search for
the total number to d
23 matches
Mail list logo
