Re: Indexing a table using a Query

On Thu, 15 Dec 2005 11:55:03 -0800, Wez wrote:

>Hi,
>
>I have a table as follows:
>
>ID Forename Surname
>------ ------------- ---------------
>2 John Rodgers
>1 Mike Williams
>3 Joe Browne
>4 Pete Anthony
>5 Ken Browne
>
>I would like a query or group of querys that will change the index to
>reflect the alphabetical order of the Surname, Forename. I want the index to
>start at one and increment by one as it moves through the records. An example
>of the result set for the above table is as follows:
>
>ID Forename Surname
>------ ------------- ---------------
>1 Pete Anthony
>2 Joe Browne
>3 Ken Browne
>4 John Rodgers
>5 Mike Williams
>
>Any suggestions would be greatly appreciated!
>
>Wes.

Hi Wes,

First suggestion: don't do it. You'll have to re-calculate the values
after each data modification, which is lots of work for little gain. If
you require such a ranking column in your output, calculate it on the
fly when querying the data. Put the calculation in a view if you need it
often. (But if you can number the rows client side, that would even be
better).

SELECT a.Forename, a.Surname,
COUNT(*) AS Rank
FROM YourTable AS a
INNER JOIN YourTable AS b
ON b.Surname < a.Surname
OR (b.Surname = a.Surname AND b.Forename <= a.Forename)
GROUP BY a.Forename, a.Surname

(untested - see www.aspfaq.com/5006 if you prefer a tested reply).

Note that I changed the column name from ID to Rank. The name "ID" is
usually used for an identifying value (key). Keys should not change once
the data is in the table. Consider what the effect would be if SSN would
be allocated based on your position in an alphabetic ordering of all US
citizens - each time someone is born or dies, all people that are
alphabetically "after" that person get a change of their SSN!!).

Best, Hugo
--

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