ORDER BY

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

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

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.

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?

Something along these lines might work.

select * from table
where <field> !=1
order desc by <field>

union

select * from table
where <field>=1;

or if the last record is always the same, hard code the values in the
sql instead of querying the table, which will save a few milliseconds :-)

If it is the "other" and not the "1" that you want last, change the
first query where clause to where <field> != 'Other' & similarly change
the second one.

Cheers,

Brent Wood

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'

Alas, this suggestion is wrong on two counts: (a) UNION expects a single
ORDER BY that applies to the whole recordset and which has to come at
the end; (b) UNION re-sorts anyway (it needs to eliminate the dupes) --
maybe you are thinking UNION ALL? So, to follow your advice he may want
a query like this, although it seems quite silly and there still isn't
an ironclad guarantee re. the final result sorting:

select * from
(select * from foo where name != 'Other' order by name) x
union all
select * from foo where name = 'Other'

On Wed, 2006-11-15 at 14:59 -0800, George Pavlov wrote:
Alas, this suggestion is wrong on two counts: (a) UNION expects a single

ORDER BY that applies to the whole recordset and which has to come at
the end; (b) UNION re-sorts anyway (it needs to eliminate the dupes)

--

maybe you are thinking UNION ALL? So, to follow your advice he may

want

a query like this, although it seems quite silly and there still isn't
an ironclad guarantee re. the final result sorting:

select * from
(select * from foo where name != 'Other' order by name) x
union all
select * from foo where name = 'Other'

Here ya go.

select 1 SortCol, * from foo where name != 'Other'
UNION ALL
select 2 SortCol, * from foo where name = 'Other'
order by SortCol;

Alternative options for what they're worth - you'd have to explain to
see how efficient they are

select id, name from (
select lower(name) as sortkey, id, name from table where name != 'Other'
union
select 'zzzzz' as sortkey, id, name from table where name = 'Other'
) as t
order by sortkey

select id, name from (
select case when name='Other' then 'zzzzz' else lower(name) end as
sortkey, id, name from table

) as t
order by sortkey

Notice that the sort will be case insensitive in these examples which
may be something that you also want.

John

George Pavlov wrote:

George Pavlov wrote:

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'

Alas, this suggestion is wrong on two counts: (a) UNION expects a single
ORDER BY that applies to the whole recordset and which has to come at
the end; (b) UNION re-sorts anyway (it needs to eliminate the dupes) --

You can also add an imaginary key to sort on, like

select 1, * from foo where name != 'Other'
union all
select 9, * from foo where name = 'Other'
order by 1;

Or independent of column order:

select 1 AS sort_key, * from foo where name != 'Other'
union all
select 9 AS sort_key, * from foo where name = 'Other'
order by sort_key;

Regards,
--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

// Integrate Your World //

In article <DFDFB8FE-1787-4508-97C4-5FD12357936A@purefiction.net>,
Alexander Staubo <alex@purefiction.net> writes:

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

Why so complicated?

SELECT whatever FROM foo ORDER BY name = 'Other', name

On 16 Nov 2006 11:04:15 +0100, Harald Fuchs <hf1110x@protecting.net> wrote:

Why so complicated?

SELECT whatever FROM foo ORDER BY name = 'Other', name

This is by far the simplest (and intelligent) of them all. And it will use a
single scan of the underlying table (or index, if used), so should prove to
be faster than the other UNION schemes.

Another tweak: If possible, use the integer column (ID) instead of the Name
column.

Regards,

--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | yahoo }.com

Hello,

a) it is right, to place an ORDER BY at the end of the statement. That is the
correct place to do this.

but

b) UNION doesn't mean anyway an sort. UNION mean "eliminate dups". But there are
several techniques to do that. One of them is "sort". So, if the development
crew chooses to change the elimination algorithme, that would be, in sight of an
UNION, ok .

Best wishes,
Regards,
Peter

---------------------------------------------------------------------------
Peter Paefgen
Landesamt für Datenverarbeitung und Statistik NRW.
Telefon: 0211 9449 2390
Fax: 0211 9449 8390
Mail: peter.paefgen@lds.nrw.de

-----Ursprüngliche Nachricht-----
Von: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] Im Auftrag von Chris Mulcahy
Gesendet: Donnerstag, 16. November 2006 00:53
An: George Pavlov
Cc: Alexander Staubo; MicroUser; pgsql-general@postgresql.org
Betreff: Re: [GENERAL] ORDER BY

On Wed, 2006-11-15 at 14:59 -0800, George Pavlov wrote:
Alas, this suggestion is wrong on two counts: (a) UNION expects a single

ORDER BY that applies to the whole recordset and which has to come at
the end; (b) UNION re-sorts anyway (it needs to eliminate the dupes)

--

maybe you are thinking UNION ALL? So, to follow your advice he may

want

a query like this, although it seems quite silly and there still isn't
an ironclad guarantee re. the final result sorting:

select * from
(select * from foo where name != 'Other' order by name) x union all
select * from foo where name = 'Other'

Here ya go.

select 1 SortCol, * from foo where name != 'Other'
UNION ALL
select 2 SortCol, * from foo where name = 'Other'
order by SortCol;

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Paefgen, Peter (LDS) wrote:

Hello,

a) it is right, to place an ORDER BY at the end of the statement. That is the
correct place to do this.

Correct - the ORDER BY at the end of the statement will apply to the
result set after the UNION has been done.
ORDER BY can be included in the sub-expression SELECT statements only if
they are enclosed in parenthesis and I believe it *must* also be
accompanied by a LIMIT clause.

but

b) UNION doesn't mean anyway an sort. UNION mean "eliminate dups". But there are

UNION means to join/combine together and if used on it's own will
eliminate duplicate rows in the result set.

Using UNION ALL will include any duplicates and run somewhat faster.

UNION itself does not imply any sort order but ORDER BY can be applied
to the result set of the UNION.

several techniques to do that. One of them is "sort". So, if the development
crew chooses to change the elimination algorithme, that would be, in sight of an
UNION, ok .

As long as the columns returned match up to satisfy the UNION criteria
then changing the elimination algorithm will not break your use of UNION.

If they want to use ORDER BY x LIMIT 2 then that sub-select will need to
be surrounded by parenthesis.

Best wishes,
Regards,
Peter

--

Shane Ambler
pgSQL@007Marketing.com

Get Sheeky @ http://Sheeky.Biz