Theodore,
Because jobnumber is declared as text, you are getting "dictionary order" (lexicographic) ordering on the values. In a dictionary, "abc" comes after "aaaaaaaaa", obviously. So indeed "999" will come after "1000". To get the effect that you want you need to treat jobnumber as a number. The easiest thing to do would be to change the declaration of the table. If for some reason you can't do that, you need to do a cast in the query; that would make your WHERE expression work, but I don't know about ORDER BY (look it up).
For example: SELECT jobnumber, jobtitle FROM jobinfo WHERE jobnumber::integer >= 200 ORDER BY jobnumber ASC;
Do you ever have non-numeric values in the jobnumber field? (Is that why it's declared as text?) If you do you will get problems because they cannot be converted to integers in order to perform the comparison.
Regards,
--Phil.
Theodore Petrosky wrote:
I give up.. what don't I understand about casting and
ints and text..
i have a table jobinfo with:
acode text, jobnumber text default nextval('public.jobinfo_seq'::text), jobtitle text
I have about 3000 rows starting with jobnumber = 1000.
SELECT jobnumber, jobtitle FROM jobinfo WHERE jobnumber >= 999 ORDER BY jobnumber ASC;
The above SQL produces no rows. however...
SELECT jobnumber, jobtitle FROM jobinfo WHERE jobnumber >= 200 ORDER BY jobnumber ASC;
produces rows with jobnumber >= 2000
if I change the query with jobnumber >= 201, I get rows >= 2010.
it is as if there was a silent zero being appended to the end of my int in the query. What am I missing, please.
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
