Any null values in first name??
-Steve
On Thu, Sep 10, 2015 at 12:35 PM, Ken Tanzer <ken.tanzer@xxxxxxxxx> wrote:
Hi. In a table that includes these columns:my_db=> \d tbl_client...name_last | character varying(40) | not nullname_first | character varying(30) | not null...I am extremely puzzled by the sorting of the "CLARKE"s in this list:my_db=> SELECT name_last,length(name_last),name_last='CLARK' AS clark,name_last='CLARKE' AS clarke FROM tbl_client WHERE name_last ILIKE 'CLARK%' OR name_last ILIKE 'ADAMS%' ORDER BY name_last || ', ' || name_first;name_last | length | clark | clarke-----------+--------+-------+--------ADAMS | 5 | f | fADAMS | 5 | f | fADAMS | 5 | f | fADAMS | 5 | f | fADAMS | 5 | f | fADAMS | 5 | f | fADAMSON | 7 | f | fCLARK | 5 | t | fCLARK | 5 | t | fCLARKE | 6 | f | tCLARKE | 6 | f | tCLARKE | 6 | f | tCLARK | 5 | t | fCLARK | 5 | t | fCLARK | 5 | t | fCLARK | 5 | t | fCLARK | 5 | t | f(17 rows)The ADAMS are included just to show a similar example is ordering correctly. I put the length and equality test columns in to try to make sure there weren't some bizarre characters in the data. This is only happening on one particular database. I did a reindex on the table just for good measure. If I remove the name_first piece of the ORDER BY (which doesn't seem like it should matter), it sorts as expected:my_db=> SELECT name_last,length(name_last),name_last='CLARK' AS clark,name_last='CLARKE' AS clarke FROM tbl_client WHERE name_last ILIKE 'CLARK%' OR name_last ILIKE 'ADAMS%' ORDER BY name_last || ', ';name_last | length | clark | clarke-----------+--------+-------+--------ADAMS | 5 | f | fADAMS | 5 | f | fADAMS | 5 | f | fADAMS | 5 | f | fADAMS | 5 | f | fADAMS | 5 | f | fADAMSON | 7 | f | fCLARK | 5 | t | fCLARK | 5 | t | fCLARK | 5 | t | fCLARK | 5 | t | fCLARK | 5 | t | fCLARK | 5 | t | fCLARK | 5 | t | fCLARKE | 6 | f | tCLARKE | 6 | f | tCLARKE | 6 | f | t(17 rows)I tried selecting those 17 rows from tbl_client into a new table, and get the same odd behavior. However, if I run with new data I get an expected order:CREATE TEMP TABLE test (name_first VARCHAR(40),name_last VARCHAR(30));INSERT INTO test VALUES ('JOE','CLARKE'),('BILL','CLARK');SELECT * FROM test ORDER BY name_last;SELECT * FROM test ORDER BY name_last || ', ' || name_first;Any thoughts about what's going on, what to do about it, or what obvious point I missing? Thanks in advance!my_db=> SELECT version();version----------------------------------------------------------------------------------------------------------------PostgreSQL 9.2.13 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-11), 64-bit(1 row)Ken--AGENCY SoftwareA Free Software data systemBy and for non-profits(253) 245-3801learn more about AGENCY orfollow the discussion.
