i have a table with some text fields filled with a data like
the same fields somethimes contains the values like
and so on.
the client requires that rows are sorted in ascending order
for the case there are a float values, i do:
SELECT ... ORDER BY float4(field)
for the case there a text values, i do:
SELECT ... ORDER BY int2(substring(field from 1 for position('x' in field)-1));
so i can sort them ascendingly at least by the first integer (before
'x' char). otherwise (simply "ORDER BY field") they were sorted as text
values - 100x100x30, 10x10x10, 400x400x30, 40x40x20, ...
now it sorted as i need - 10x10x10, 40x40x20, 100x100x30, 400x400x30 ...
it's almost fine, but...
now i need to combine that 2 cases, so i try (the field called m1):
SELECT ... ORDER BY (CASE WHEN position('x' in m1)>1 THEN int2(substring(m1 from 1 for position('x' in m1)-1)) ELSE float4(m1) END)
i never used CASE WHEN ... THEN ... ELSE ... END construct before,
& assume the above is errorneus by default.
Best regards, Sandis
pgsql-sql by date
|Next:||From: Najm Hashmi||Date: 2000-11-10 15:59:28|
|Previous:||From: Kovacs Zoltan Sandor||Date: 2000-11-10 11:08:50|
|Subject: Re: Return from stored procedures|