how to write query for rownuber in alphabet

29 Dec 2005 6:04 AM

jaffar

sir,

i have a doubt related to rownumber,in my application i want to use
the row numbers in alphabets, i know how to print the rownumber in numeric
but i want in alphabet for example

SELECT (SELECT COUNT(*) FROM authors e2 WHERE e2.au_id <= e.au_id) AS
rownumber, au_id, au_lname FROM authors e ORDER BY au_id

the result will be like
ROWNUMBER
1
2
3
..
..
..
but i want in alphabet like
ROWNUMBER
A
B
C
D
E
F
..
..
..
if any one knows please send me sql query immediatly.

29 Dec 2005 7:21 AM

Uri Dimant

hI

SELECT *,CASE rownumber WHEN 1 THEN 'A'
                      WHEN 2 THEN 'B'
        WHEN 3 THEN 'C'
        WHEN 4 THEN 'D'
        WHEN 5 THEN 'F'
                      ELSE 'NONE'
                      END AS new_one
FROM
(
SELECT (SELECT COUNT(*) FROM authors e2 WHERE e2.au_id <= e.au_id) AS
rownumber, au_id, au_lname FROM authors e
) AS der
ORDER BY rownumber
Show quote

"jaffar" <jaffar***@nannacomputers.com> wrote in message
news:%23qgkw2DDGHA.1816@TK2MSFTNGP11.phx.gbl...
> sir,
>
> i have a doubt related to rownumber,in my application i want to use
> the row numbers in alphabets, i know how to print the rownumber in numeric
> but i want in alphabet for example
>
> SELECT (SELECT COUNT(*) FROM authors e2 WHERE e2.au_id <= e.au_id) AS
> rownumber, au_id, au_lname FROM authors e ORDER BY au_id
>
> the result will be like
> ROWNUMBER
> 1
> 2
> 3
> .
> .
> .
> but i want in alphabet like
> ROWNUMBER
> A
> B
> C
> D
> E
> F
> .
> .
> .
> if any one knows please send me sql query immediatly.
>

29 Dec 2005 2:28 PM

Raymond D'Anjou

Show quote

"Uri Dimant" <u***@iscar.co.il> wrote in message
news:eU9BNiEDGHA.2704@TK2MSFTNGP11.phx.gbl...
> hI
>
> SELECT *,CASE rownumber WHEN 1 THEN 'A'
>                      WHEN 2 THEN 'B'
>        WHEN 3 THEN 'C'
>        WHEN 4 THEN 'D'
>        WHEN 5 THEN 'F'
>                      ELSE 'NONE'
>                      END AS new_one
> FROM
> (
> SELECT (SELECT COUNT(*) FROM authors e2 WHERE e2.au_id <= e.au_id) AS
> rownumber, au_id, au_lname FROM authors e
> ) AS der
> ORDER BY rownumber

Hi Uri,
You forget to say... "Better to do this client side and not in SQL".

Let's simplify a bit:
SELECT *, char(rownumber + 64) AS new_one
FROM
(
SELECT (SELECT COUNT(*) FROM authors e2 WHERE e2.au_id <= e.au_id) AS
rownumber, au_id, au_lname FROM authors e
) AS der
ORDER BY rownumber