sorting and marking fields

[agilo]

Hi,

I do have a small problem to mark data entries within a field.
for example,

1231 120
1231 80
1231 50
2135 30
2135 15

I would like to sort the second field ascending and mark the values (A, B, C ,.... etc) as following

1231:50:A
1231:80:B
1231:120:C
2135:15:A
2135:30:B

Can any body helps.

Thanks in advance,

agilo

 

[r937]

you can do this in a three-step process

first, create a temporary table with a compound primary key consisting of your first column together with a second auto_increment column

then run a select query which orders your rows into the correct sequence, and populate the temporary table

this will give you rows in the temp table like this --

1231 1 50
1231 2 80
1231 3 120
2135 1 15
2135 2 30

last step, select from this table and translate the auto_increment number into a letter

one method is to use the number 1,2,3,etc. as an offset into a letter string, i.e.

substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ',autoinc,1)


r937.com | rudy.ca

 

[agilo]

Thank you for your help,

The problem I want to do it using SQL+, could you please clear a little bit the way to mark the rows (1,2,3,. etc)

 

[r937]

i don't think you can do it in sql+

by the way, i should like to take this opportunity to point out that you're in the wrong forum, this is mysql



r937.com | rudy.ca