sort numerically by character string in query

[eli22]

Hi. I have a field which might come in as a numeric but it's usually character. It might have numbers in it. And I use that field to define groups for processing.

Until now this has been my query to define the groups

SELECT myfield FROM mytable GROUP BY 1

But when there are many numerically-named groups, they show up out of order:
1
10
2
3
etc.

I want to change the query to

SELECT VAL(myfield) FROM mytable GROUP BY 1

But then the group names become numeric - we want character group names.

Is there a way to add an ORDER BY clause to the original query, that sorts according to the numeric value? I didn't have luck with that. This would also be better because if I get data like: 1tree, 2tree, 3tree, 4tree, then I can sort it also. (I'll add a test whether the field actually has a numeric or not before sorting it this way, and when it doesn't I'll use the old query.)

Thank you,
Ellen

 

[EzLogic]

Select myField from Mytable group by 1 order by val(myField) ASC (or you can do DESc)


Ez Logic
Michigan

 

[Mike Lewis]

Alternatively:

[tt].... ORDER BY TRANSFORM(MyField, "9999999999")[/tt]

That way, you don't have to pre-test for numeric. TRANSFORM() will give you the same result regardless of whether the field is numeric or character.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Index, since you only want to change the order and not the groups, the solution is to make use of the order by clause. Order by can be done on the cardinal field number (order by 1) by field names (order by myfield) or by expressions, eg one of the expressions the others recommended.

You can of course also SELECT field, val(field), * FROM table GROUP BY 1 ORDER BY 2, but being able to order by an expression means you don't need to have this expression in the result fields.

Bye, Olaf.

 

[eli22]

Olaf you have mentioned the issue which must be the problem I have when trying the order by that Mike & Ez suggest, because their queries give me "order by clause is invalid"

So you're right I'd rather not have to bring 2 columns into the results but that seems to be what works.

And the data is very massive I don't want to start indexing it. But perhaps the in-between solution is to select into one cursor, set the order and then select it again. What do you think?

Thank you.

 

[Mike Lewis]

when trying the order by that Mike & Ez suggest, because their queries give me "order by clause is invalid"

I wasn't trying to give you the exact syntax, but rather the general idea. You could just as easily do:

[tt]
SELECT TRANSFORM(MyField, "9999999999") FROM MyTable ORDER BY 1[/tt]

And the data is very massive I don't want to start indexing it.

Just the opposite. The larger the data, the more reason to index it. After all, you only have to index it once, but the benefit is always there.

Mike





__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[vgulielmus]

You can try :
Select PADL(RTRIM(myField),m.nFieldLen) from Mytable group by 1

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[vgulielmus]

A quick test

nFieldLen=29
CREATE CURSOR mytable (myfield C(m.nFieldLen))
INSERT INTO mytable VALUES ('103tree')
INSERT INTO mytable VALUES ('1tree')
INSERT INTO mytable VALUES ('2tree')
INSERT INTO mytable VALUES ('10tree')
INSERT INTO mytable VALUES ('3tree')
INSERT INTO mytable VALUES ('45tree')
INSERT INTO mytable VALUES ('1tree')
INSERT INTO mytable VALUES ('10tree')
INSERT INTO mytable VALUES ('3tree')
INSERT INTO mytable VALUES ('103tree')

Select PADL(RTRIM(myField),m.nFieldLen) from Mytable group by 1

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[eli22]

Thanks everyone!