Postgres Pro
Downloads
Home   >   mailing lists

Thread: ORDER BY

ORDER BY

From
"MicroUser"
Date:
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.

Re: ORDER BY

From
Ashley Moran
Date:
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

Re: ORDER BY

From
Niklas Johansson
Date:
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

Re: ORDER BY

From
Alexander Staubo
Date:
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.

Re: ORDER BY

From
"George Pavlov"
Date:
> 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'

Re: ORDER BY

From
John Sidney-Woollett
Date:
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:
>> 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'
>
> ---------------------------(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

Re: ORDER BY

From
Alban Hertroys
Date:
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 //

Re: ORDER BY

From
Brent Wood
Date:
> 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

Re: ORDER BY

From
Harald Fuchs
Date:
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

Re: ORDER BY

From
Chris Mulcahy
Date:
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;

Re: ORDER BY

From
"Gurjeet Singh"
Date:
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

Re: ORDER BY

From
"Paefgen, Peter (LDS)"
Date:
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

Re: ORDER BY

From
Shane Ambler
Date:
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