Hi.

I have a table:

ID | Name
0 | Anna
1 | Other
2 | Link
3 | Fernando

I need sorted result but the way like this:

0 | Anna
3 | Fernando
2 | Link
1 | Other

Record '1 | Other' must by at the end of query result.

How I can get it?

Thx.

On Nov 14, 2006, at 10:03 pm, MicroUser wrote:

> Hi.
>
> I have a table:
>
> ID | Name
> 0 | Anna
> 1 | Other
> 2 | Link
> 3 | Fernando
>
> I need sorted result but the way like this:
>
> 0 | Anna
> 3 | Fernando
> 2 | Link
> 1 | Other
>
> Record '1 | Other' must by at the end of query result.
>
> How I can get it?
>
> Thx.
>


I suppose a nasty way would be with something like
ORDER BY CASE "Name" WHEN 'Other' THEN 'zzzzzzzzz' ELSE "Name" END

But this might work well enough if you've only got a few rows in the
table

Ashley

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On 14 nov 2006, at 23.03, MicroUser wrote:
> I need sorted result but the way like this:
>
> 0 | Anna
> 3 | Fernando
> 2 | Link
> 1 | Other
>
> Record '1 | Other' must by at the end of query result.
>
> How I can get it?


Well, maybe not the answer you're looking for, but a rather clean way
to do this would be to only store actual names in the table, let your
application do the select and sort, and then add the 'Other' at runtime.

If the 'Other' needs to be stored, perhaps it could be represented
with a NULL value instead? (It's not really a name, just a
placeholder for not knowing, isn't it?)


Sincerely,

Niklas Johansson
Phone: +46-322-108 18
Mobile: +46-708-55 86 90




---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

On Nov 14, 2006, at 23:03 , MicroUser wrote:

> I need sorted result but the way like this:
>
> 0 | Anna
> 3 | Fernando
> 2 | Link
> 1 | Other
>
> Record '1 | Other' must by at the end of query result.

It's not apparent from your example that you want something other
than a purely lexicographic sort order (after all, "Other" comes
after "Link", "Fernando" and "Anna", so "order by name" already gets
you what you want), but I assume that's what you mean.

If your table is sufficiently small, and the complexity of the actual
query sufficiently low, prepending an expression sort key might suffice:

select * from foo
order by (case name when 'Other' then 1 else 0 end), name

Note that PostgreSQL is slow at evaluating case expressions, and this
might prove too slow. For larger tables, you may have to resort to a
union:

select * from foo where name != 'Other' order by name
union
select * from foo where name = 'Other'

Alexander.

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/