I vant to SORT a recordset Query 1

[qwert231]

In my ASP page I have a SQL query that gives me a single field from a table. But the recordset is populated using the 1, 10, 11, 2 method... whatever you call it. I want it to go 1,2,3...10,11,

How can I do that?

 

[CDN]

Simply change your SQL
to:

[tt]SQL="SELECT * FROM Your Table ORDER BY Field to sort ASC"[/tt]


[tt]ASC = Ascending order
Des = Descending order
[/tt]
Hope it helps...

 

[qwert231]

K, cept Des give me this error:
Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
Line 1: Incorrect syntax near 'DES'.
/includeSets.asp, line 143

And Asc still gives me this:
1
10
11
13
15
16
17
18
2
3
4
5
6
7
8
9

 

[CDN]

the statement should end like this:

ORDER BY Your field desc"
and not ORDER BY Your field 'desc'

My bad, had a typo.

 

[David75]

If you're still getting the records sorted like "1, 10, 11, 12" then you're probably not using a number data type for that field - more likely it's a string type. Check your data type.

 

[qwert231]

What if I cannot change the datatype? what if there is another application using this database?

 

[BBirkett]

Depends on the database you are using... their maybe some functions you can use which are part of your databases unique api. Brett Birkett B.Comp
Systems Analyst

 

[qwert231]

MS SQL Server 2000.

 

[Turkbear]

Try converting the value to a number before the order by :

SQL ="SELECT * FROM Your Table ORDER BY to_number(Field to Sort) ASC"


( This may not be SqlServer ( it works in Oracle) but you should get the idea..)
SQL> select * from temp_testx order by f1 asc;

F1
--
1
10
2
20

1* select * from temp_testx order by to_number(f1) asc
SQL> /

F1
--
1
2
10
20

 

[qwert231]

Has anybody used the Convert funtion in a SQL query? Ex. from 'SQL Server 2000 Programming By Example' (By Que Publishing):

SELECT orderid, customerid, employeeid, orderdate FROM Orders WHERE CONVERT(VARCHAR(20),orderdate,102) = '1996.07.04'

I would like more information on how that works. (Pouring thru the book as we speak.)

 

[qwert231]

Okay, I got it. Here's the final Search string. It takes the data which is saved as char and converts it to int, then of course sorts ascending. I'm so proud of myself.

SELECT Distinct ID = CONVERT(int, ID) FROM CustomerProducts WHERE CustomerID = '&quot; & custid & &quot;' AND ProgramID = '&quot; & searchID & &quot;' AND ID <> 'SLT' Order BY ID Asc