CODEWITHSUNDEEP
mysql
Carl
Carl

Reputation: 43

the sort result is affected by the length of varchar column mysql

I encontered a strange issue which the length of varchar affects the result in the mysql db.

The sql:

SELECT oid, 
CODE, 
VALUE,
COMMENT,
obser_date, 
condition_code, 
date_asserted, 
location_code, 
patient_oid, 
performer
FROM t_clinical_observation
WHERE patient_oid= 1 AND CODE='1101' ORDER BY obser_date DESC

The oid is auto increase and comment is varchar.

If the lengh of comment is 512, the result is: enter image description here

If the lengh of comment is 128, the result is: enter image description here

I wanna the record of oid 109 always keep at first. But if the length of comment column is 512, it stays at second row. If the length of comment column is 128, it stays at first row.

Thanks.

Upvotes: 0

Views: 35

Answers (1)

Insac
Insac

Reputation: 810

There is no issue: you're sorting for the date field and the two records have exactly the same date, so they can appear in whatever order they want.

The database could also return them in different order if you call the same query twice. If you really see a correlation between the size of the field and the order of the two rows, it might be that the database is using two different execution plans that bring the data in different order, depending on the size of the field.

However, if you want to have a deterministic order into your resultset, you should order for a set of fields that identify uniquely your rows (in your case it might be obser_date AND oid)

Upvotes: 1

Related Questions