Textpattern CMS

#1 2012-07-17 17:03:49

richtestani

Plugin Author

Registered: 2009-11-08

Posts: 128

Why is this sorting like this?

I have a handful of records, which I am sorting by number. The following SQL:

 select Title, url_title, unix_timestamp(Posted) as uPosted, unix_timestamp(Expires) as uExpires, unix_timestamp(LastMod) as uLastMod from eidm_textpattern as textpattern where 1=1 and Status = 4 and Posted <= now() and (now() <= Expires or Expires = '0000-00-00 00:00:00') and Section IN ('cases') order by url_title + 0 asc limit 0, 100

seems to output this (first handful):

$4.825 Million Late Diagnosis/Treatment of Bacterial Meningitis	late-diagnosis-treatment-of-bacterial-meningitis	1335446843	0	1340900869
Confidential Sum Diabetic Wound Care	confidential-sum-diabetic-wound-care	1335471544	0	1342041560
$1.3 Million Failure to Diagnose Tendon Injury	13-million-failure-to-diagnose-tendon-injury	1335469512	0	1340721127
$4.0 Million for Birth Trauma	40-million-for-birth-trauma	1335468393	0	1342023907
$100,000 Fall On Icy Sidewalk	100000-fall-on-icy-sidewalk	1335472527	0	1342470283

…100k through 900k

And then up in amounts. If I change sorting to title, it sorts properly at the top but towards the end falls apart.

…4.8 million through 450k…

$450,000 Fractured Skull	450000-fractured-skull	1335471103	0	1340383736
$425,000 Negligent Cosmetic Surgery	425000-negligent-cosmetic-surgery	1335471147	0	1340721590
$360,000 Fractured Leg	360000-fractured-leg	1335471209	0	1340723031
Confidential Sum Diabetic Wound Care	confidential-sum-diabetic-wound-care	1335471544	0	1342041560
$100,000 Fall On Icy Sidewalk	100000-fall-on-icy-sidewalk	1335472527	0	1342470283
$150,000 Slip On Broken Sewer Grate	150000-slip-on-broken-sewer-grate	1335473100	0	1340901095
$375,000 Single Car Collision	375000-single-car-collision	1342538293	0	1342538293

…360k – 100k…

Why would the textual title come in between any of them?
Thanks
Rich

#2 2012-07-17 17:33:48

CeBe

Plugin Author

From: Caen - Fr

Registered: 2010-06-25

Posts: 345

Website

Re: Why is this sorting like this?

I guess it’s because ordering by url_title + 0 converts the sort criteria from a string to an integer.
What happens if you remove + 0 ?