TSQL: Howto add a char to a select statement

Question

I have the following statement

SELECT id, descr from mytable 

which returns

1, 'Test1'
4, 'Test4'
6, 'Test6'
8, 'Test8'
22, 'Test22'

Now I want the display to Add a sequential character to the first 4 results...

'A', 1, 'Test1'
'B', 4, 'Test4'
'C', 6, 'Test6'
'D', 8, 'Test8'
'',22, 'Test22'

Thoughts?

Edit: Would prefer a SQL Server 2000 example if possible.

Answer 1

SELECT CASE WHEN ROWNUMBER < 4
           THEN CHAR(65 + ROWNUMBER - 1)
           ELSE ''
       AS <WHATEVER>
       ,X.id
       ,X.descr
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY <WHATEVER>) AS ROWNUMBER
        ,id
        ,descr
    FROM mytable
) AS X

This in SQL Server 2005 and up.

In SQL Server 2000 (assuming id is your sort order and unique):

SELECT CASE WHEN rownumbers.rownumber < 4
           THEN CHAR(65 + rownumbers.rownumber - 1)
           ELSE ''
       AS <WHATEVER>
       ,mytable.id
       ,mytable.descr
FROM (
    SELECT l.id, COUNT(*) AS rownumber
    FROM mytable AS l
    LEFT JOIN mytable AS r
        ON l.id >= r.id
    GROUP BY l.id
) AS rownumbers
INNER JOIN mytable
    ON mytable.id = rownumbers.id