Help with returning a certain # of records from a view.

[Mike]

I have a view that will return say 5000 records when I do a simple
select query on that view like.

select *
from vw_test_view
How can I set up my query to only return a certain # of records, say
the first 300?
Here is what is going on, we have a large amount of data that returns
in a view and we need to work with all of it eventually, However we
want to do it in chunks. So my thoughts were as follows:

1. To run a query to return X amount of the total data for us to work
with.
2. Update these records with a flag in a table that the vw_test_view
filters out.
3. The next time I run the query to pull data from the view it will
skip the records that I have already looked at (because of step 2) and
pull the next X amount of records.

Thanks in advance,
Mike

[Hugo Kornelis]

On 24 Jun 2004 08:43:30 -0700, Mike wrote:

I have a view that will return say 5000 records when I do a simple
select query on that view like.

select *
from vw_test_view
How can I set up my query to only return a certain # of records, say
the first 300?
Here is what is going on, we have a large amount of data that returns
in a view and we need to work with all of it eventually, However we
want to do it in chunks. So my thoughts were as follows:

1. To run a query to return X amount of the total data for us to work
with.
2. Update these records with a flag in a table that the vw_test_view
filters out.
3. The next time I run the query to pull data from the view it will
skip the records that I have already looked at (because of step 2) and
pull the next X amount of records.

Thanks in advance,
Mike

Hi Mike,

You could use the TOP clause of the SELECT statement:

SELECT TOP 300 Column1, Column2, ....
FROM MyView
WHERE ..... -- if necessary
ORDER BY ......

Without the order by, you'll still get maximum 300 rows, but there's no
way predicting which 300 out of the total number of matching rows will be
selected. With the ORDER BY, you'll get the first 300 according to the
specified sort order.

An alternative is to use SET ROWCOUNT:

SET ROWCOUNT 300
SELECT Column1, Column2, ....
FROM MyView
WHERE ..... -- if necessary
ORDER BY ......
SET ROWCOUNT 0 -- restored default behaviour

The SET ROWCOUNT gives the maximum number of rows to affect for all future
commands from the same connection. Note that this applies to UPDATE and
DELETE as well!! To return to the default behaviour of affecting all rows,
use SET ROWCOUNT 0 or close and re-open the connection.

Note that both methods use proprietary Transact-SQL syntax. An ANSI
standard version can only be done with a specified order (you'll have to
specify by which order you want the 300 "first" rows) and requires a
correlated subquery. It will be much slower.

SELECT Column1, Column2
FROM MyView AS a
WHERE ..... -- if necessary
AND (SELECT COUNT(*)
FROM MyView AS b
WHERE ..... -- same as in outer join
AND b.OrderingColum n < a.OrderingColum n)
< 300
ORDER BY OrderingColumn -- may be omitted
Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[--CELKO--]

>> How can I set up my query to only return a certain # of records
[sic], say
the first 300? <<

Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access
or ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless.

You will have to get out the RDBMS world and use a cursor of some
kind.

Here is what is going on, we have a large amount of data that

returns
in a view and we need to work with all of it eventually, However we
want to do it in chunks. <<

1) A mere 5000 rows is not a lot of data.

2) The idea of "doing it in chunks" is dangerous; do you know anything
about transactions, isolation levels and shared data?

[Michael Rea]

Sometimes it benefits programmers to get out of in front of their
screens for a while and see how what they do affects end users.
Unfortunately too many of them do not take the time to do this or to try
and understand things from an end users point of view. No 5000 rows is
not a lot of data from a programmers point of view, but from a user who
has to go through this and verify certain information this can seem like
a daunting task, if you can break it down either feed it to them slowly
or split it amongst several people it becomes much more manageable for
them. This by the way is not what I am trying to accomplish, nor is
5000 the # of rows that I have of total data or 300 how many that I want
to pull out at a time. All that this is are made-up scenarios to
illustrate the type of things that I am trying to accomplish.

If you want to crucify me with semantics go ahead. It doesn't matter,
all that does is that people understand my question and through their
generosity point me in the right direction.

Hugo, thanks again for the help this will give me what I need to get the
job done.
And I already have the view using an order by clause on the data and it
returns exactly what I need, so if I add in the top clause it should
give me exactly what I need.

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!