# SQL ORDER BY function not working?



## mrdomer07 (Dec 30, 2009)

Hello- I am trying to sort a varchar field in a particular table and cannot seem to get it to sort correctly. Here is the code:

INSERT INTO E_POINTS
SELECT
c1,
c2,
c3,
c4,
c5,
c6
FROM E_POINTS_TEMP
ORDER BY c1, c2


c1 is a column is a varchar and is the column that is not sorting as I think it should. There are several million records in E_POINTS and 99.99% of them are sorting correctly, but each time that I try to sort the first column there is an error in the sort about 1,000 records in. Here is the output for c1 where the sorting does not appear to be working properly:

...
500159382O
500160770K
500160770K
500160770K
500160770K
500161081A
500161081A
500161081A
500161081A
500161081A
G99984289G
G99984289G
...
...(a few hundred records)
...
G99989178G
G99989178G
G99989178G
G99989178G
G99989178G
500161081A
...

Afterwards, the sort appears to be working correctly. Any ideas?


----------



## jamiemac2005 (Jul 5, 2007)

Hey, i know it's not the best of explanations but i think the problem is that you're sorting on insert... which SQL client are you using?

Basically order by sorts the result of something, but the result of an insert statement is usually the amount of rows affected... Can you sort the data on the way out? why does it need to be sorted within the table? and if worse comes to worse rewrite the table using a select statement to order the data then an insert one to insert each value in that order.


----------



## mrdomer07 (Dec 30, 2009)

I am using the SQL Query Analyzer, v. 8.0.

Admittedly Im not too knowledgeable about SQL but I think I understand your point. The table used above (E_POINTS) is used in conjunction with a similar table called R_POINTS. In an executable program, I simulataneously run a cursor down each of those two tables. If the tables are not sorted properly by c1, then the code enters an inifinite loop. So in my understanding, no, I cannot sort on the way out.

I noticed you mentioned sorting by a select statement- I am not familiar with this kind of syntax. Could you provide some more detail here?

Lastly, I wanted to note that upon restarting the computer and attempting to sort the table again I had no problems with the order by function. Although this is fine for a one time fix, I have to run this program every few months and I have had similar sort problems in the past. A longer term solution would certainly be appreciated. Thanks in advance.


----------



## bmdsherman (Jan 16, 2010)

Try this on line 10:
ORDER BY c1 DESC, c2 DESC
(or ASC)


----------

