CODEWITHSUNDEEP
mysqlsql
jaya
jaya

Reputation: 327

how to sort this data by in ascending order?

Am trying to retrieve data from Database in Ascending order.

My query was 

select pid 
from `patient_list` t1, patient_info t2 
where pid > '2000' and t2.id=t1.id 
order by pid asc 
limit 10

but the data is like 

pid
2221
2223
2224
2227
**223**
2238
2239
2242
2245
2247
**225**

How to sort this ?

Upvotes: 0

Views: 100

Answers (4)

Tschitsch
Tschitsch

Reputation: 1338

your pid-columns seems not to be a numeric fieldtype, so the values are treaten as string values. in your case the order-by-algorithm is sorting the whole string char by char and not by the actual value of the number

2227, 223, 2238 will sort like AAAG, AAB, AABG

i assume its varchar, try to change it to INT

Upvotes: 0

Mosty Mostacho
Mosty Mostacho

Reputation: 43494

Firstly, clean the data. Secondly, cast to int. Thirdly sort and/or filter.

select pid from `patient_list` t1, patient_info t2 
where replace(pid, "*", "") + 0 > 2000 and t2.id = t1.id 
order by replace(pid, "*", "") + 0
limit 1

Note that you are also filtering on that field so you should use the replacement logic twice.

Upvotes: 0

K T
K T

Reputation: 81

In order to do that, you should sort by both length of pid and itself. This trick will work even for non-integer pids.

select pid from patient_list t1, patient_info t2 
where length(pid) >= 4 and pid > '2000' and t2.id=t1.id 
order by length(pid) asc, pid asc limit 1

Upvotes: 0

juergen d
juergen d

Reputation: 204884

Your data is sorted alphanumeric. To force a numerical sort you must cast the data into a number. For instance with pid * 1

select pid 
from `patient_list` t1, patient_info t2 
where pid > '2000' 
and t2.id=t1.id 
order by pid * 1 asc 
limit 1

Since your pid is of a string type you should consider changing that to an int.

Upvotes: 6

Related Questions