sql server mseq: 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.

[quoted text, click to view]

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
--