row index

how can i get the row index using the "SELECT" clause?
say i have a 100-row table, and i select one that meets certain critiria.
how do i get that particular row's row index.
and also, given a row index, how can i select the row of that index?
i am looking for best practice because my table can potentially be huge
(100,000 or more rows).
- thanks.What do you mean by "row index"? SQL Server doesn't expose any internal
row identifiers to the outside world. Rows should be identifiable by a
key which is made up of some subset of columns in the table. You define
keys with a PRIMARY KEY or UNIQUE constraint.
David Portas
SQL Server MVP
--|||How to dynamically number rows in a SELECT Statement
http://support.microsoft.com/defaul...kb;en-us;186133
AMB
"Harry" wrote:

> how can i get the row index using the "SELECT" clause?
> say i have a 100-row table, and i select one that meets certain critiria.
> how do i get that particular row's row index.
> and also, given a row index, how can i select the row of that index?
> i am looking for best practice because my table can potentially be huge
> (100,000 or more rows).
>
> - thanks.
>
>|||thanks. this worked. however it is much too slow. i have a table of 500 rows
now, and it almost takes a full second to query. any faster solutions?
basically what i want to do is the following:
i have an entity table of say 1000 rows. they have their own primary keys
(int). when i select one enity based on its own key, i want to get the
previous and next entity's key based on the alphabetically order of the
entity name.
for example, here is my table
key name
1 smith
2 calvin
3 williams
4 borg
5 albert
...
now when i select "borg", i want to figure out the key of "albert" and
"calvin" because they are the previous and next entity alphabetically. this
must be a common problem, imagining you want to show the previous and next
record of current record based on alphabetically order of the record name
instead of the numerical order of the record key.
i built a view using the MS KB article. but i had to query that view 4 times
to get both keys. maybe my queries are a bit clumsy, but each query took
about 1 second to come back. that's pretty slow considering i only have
about 500 rows now. is there a way to "cache" a view so that everytime when
you query it, it goes to the cache instead of re-build the table again? or
maybe i will have to maintain a separate key column to record the
alphabetical order of the record name?
thank you very much.
- harry
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:64E3B6A2-E763-418C-A80C-280806E17E0F@.microsoft.com...
> How to dynamically number rows in a SELECT Statement
> http://support.microsoft.com/defaul...kb;en-us;186133
>
> AMB
> "Harry" wrote:
>|||Sorry to answer with another question but why do you want to know the
"next" and "previous" key? Maybe you are paging a result set, in which
case refer to:
http://www.aspfaq.com/show.asp?id=2120
In SQL rows are accessed by key not by any logical "position", which is
why there isn't necessarily a straightforward answer to your question.
Normally keys are sufficient for data access purposes and things like
positional order on screen can be left to the front end or middle tier.
You could access the "next" value in a sequence with a query like
SELECT MIN(name) AS next_name
FROM YourTable
WHERE name > @.current_name
Hope this helps.
David Portas
SQL Server MVP
--|||>
> SELECT MIN(name) AS next_name
> FROM YourTable
> WHERE name > @.current_name
>
this is exactly what i need. sorry i made it sounds so complicated!
thanks!
- harry
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1110990570.235452.46000@.o13g2000cwo.googlegroups.com...
> Sorry to answer with another question but why do you want to know the
> "next" and "previous" key? Maybe you are paging a result set, in which
> case refer to:
> http://www.aspfaq.com/show.asp?id=2120
> In SQL rows are accessed by key not by any logical "position", which is
> why there isn't necessarily a straightforward answer to your question.
> Normally keys are sufficient for data access purposes and things like
> positional order on screen can be left to the front end or middle tier.
> You could access the "next" value in a sequence with a query like
> SELECT MIN(name) AS next_name
> FROM YourTable
> WHERE name > @.current_name
> Hope this helps.
> --
> David Portas
> SQL Server MVP
> --
>|||> SELECT MIN(name) AS next_name
> FROM YourTable
> WHERE name > @.current_name
sorry to keep asking stupid questions. but how can i get the id of this
record out of the same query?
obviously i could record down the "name", and do a look up to find out its
id. but is there an easier way?
thanks again.
- Harry
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1110990570.235452.46000@.o13g2000cwo.googlegroups.com...
> Sorry to answer with another question but why do you want to know the
> "next" and "previous" key? Maybe you are paging a result set, in which
> case refer to:
> http://www.aspfaq.com/show.asp?id=2120
> In SQL rows are accessed by key not by any logical "position", which is
> why there isn't necessarily a straightforward answer to your question.
> Normally keys are sufficient for data access purposes and things like
> positional order on screen can be left to the front end or middle tier.
> You could access the "next" value in a sequence with a query like
> SELECT MIN(name) AS next_name
> FROM YourTable
> WHERE name > @.current_name
> Hope this helps.
> --
> David Portas
> SQL Server MVP
> --
>|||Sure, not-ANSI, but there is a way:
SELECT TOP 1 id, name
FROM YourTable
WHERE name > @.current_name
ORDER BY name
BG, SQL Server MVP
www.SolidQualityLearning.com
"Harry" <ninghai@.gmail.com> wrote in message
news:%23%23FOkDlKFHA.572@.tk2msftngp13.phx.gbl...
>
> sorry to keep asking stupid questions. but how can i get the id of this
> record out of the same query?
> obviously i could record down the "name", and do a look up to find out its
> id. but is there an easier way?
> thanks again.
> - Harry
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1110990570.235452.46000@.o13g2000cwo.googlegroups.com...
>|||great thanks.
-
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:eB2XFdoKFHA.2936@.TK2MSFTNGP15.phx.gbl...
> Sure, not-ANSI, but there is a way:
> SELECT TOP 1 id, name
> FROM YourTable
> WHERE name > @.current_name
> ORDER BY name
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Harry" <ninghai@.gmail.com> wrote in message
> news:%23%23FOkDlKFHA.572@.tk2msftngp13.phx.gbl...
>