andrew gatt <[EMAIL PROTECTED]> wrote:
I'm trying to work out the most efficient way of performing a query.
What i want is for the rest of the database starting at a certain
letter, and ordered alphabetically. So for example this command:
SELECT name FROM people WHERE name > 'C%' ORDER BY people
however i need to apply a limit and my application will find this
easier if i know of the offset of where the first row is returned. So
i think i can get this with:
SELECT COUNT name FROM people WHERE name < 'C%' ORDER BY people
but this is obviously scanning the database twice for the same
information, the first query knows the offset of where is starts but
i can't think of how to get the information out of the query.
Actually, the first query does not necessarily know the offset where it
starts, if the condition happens to be satisfied with an index. If there
is an appropriate index on name, then running two querties is not
actually a waste of time - the operations they need to perform do not
overlap.
If there is no suitable index on name, then you might consider simply
doing
select name from people order by name;
without the condition. Then you programmatically scroll through the
resultset until you encounter a name starting with 'C', counting records
up to that point. This may be cheaper than running two queries that both
just scan the whole table.
If you insist on doing it in a single query, try this:
select count(*), (case when name >= 'C' then name else '' end)
filteredName
from people
group by filteredName
order by filteredName;
The first record would give you the count of records with name < 'C',
the rest would contain individual names >= 'C' in order (with a count of
1, unless you have duplicate names). Not sure how efficient this is.
It'll very likely disqualify any index you might have on name.
Igor Tandetnik
