Textpattern Forum

You are not logged in. Register | Login | Help

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

richtestani
Plugin Author
Registered: 2009-11-08
Posts: 92

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

Offline

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

CeBe
Plugin Author
From: Caen - Fr
Registered: 2010-06-25
Posts: 285
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 ?

Offline

Board footer

Powered by FluxBB