Thread: order by question

Hi folks.

I seem to remember somewhere being shown how to bump specific rows to 
the top of a list; something along the lines of:

select c_id as key, c_des as value from customers order by c_id = 7, 
c_id = 160, value;

however, although the statement is accepted the two rows specified are 
not bumped to the top of the list, but instead appear in their correct 
position in the order by value part.

Is it possible and if so how do I do it?
-- 
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000     

Gary Stainburn wrote:
> Hi folks.
> 
> I seem to remember somewhere being shown how to bump specific rows to 
> the top of a list; something along the lines of:
> 
> select c_id as key, c_des as value from customers order by c_id = 7, 
> c_id = 160, value;

Looks roughly right.

SELECT * FROM foo ORDER BY not(a=6),not(a=4),a; a |  b   |  c
---+------+----- 6 | ccc  | BBB 4 | aaa  | BBB 1 | aaa  | AAA 2 | zxxx | AAA 3 | ccc  | ZZZ 5 | zxxx | BBB
(6 rows)

Alternatively: (a<>6),(a<>4),a

--  Richard Huxton  Archonet Ltd

O Gary Stainburn έγραψε στις Mar 9, 2005 :

> Hi folks.
> 
> I seem to remember somewhere being shown how to bump specific rows to 
> the top of a list; something along the lines of:
> 
> select c_id as key, c_des as value from customers order by c_id = 7, 
> c_id = 160, value;

use the 
case ... when .. then ... when ... then ... else ...
construct.
> 
> however, although the statement is accepted the two rows specified are 
> not bumped to the top of the list, but instead appear in their correct 
> position in the order by value part.
> 
> Is it possible and if so how do I do it?
> 

-- 
-Achilleus

On Wed, Mar 09, 2005 at 12:41:55 +0000, Gary Stainburn <gary.stainburn@ringways.co.uk> wrote:
> Hi folks.
> 
> I seem to remember somewhere being shown how to bump specific rows to 
> the top of a list; something along the lines of:
> 
> select c_id as key, c_des as value from customers order by c_id = 7, 
> c_id = 160, value;
> 
> however, although the statement is accepted the two rows specified are 
> not bumped to the top of the list, but instead appear in their correct 
> position in the order by value part.

Are you sure? It looks like you are going to have them appear at the bottom
of the list doing the above. Remember that false sorts before true.

On Wednesday 09 March 2005 1:06 pm, you wrote:
> Gary Stainburn wrote:
> > Hi folks.
> >
> > I seem to remember somewhere being shown how to bump specific rows
> > to the top of a list; something along the lines of:
> >
> > select c_id as key, c_des as value from customers order by c_id =
> > 7, c_id = 160, value;
>
> Looks roughly right.
>
> SELECT * FROM foo ORDER BY not(a=6),not(a=4),a;
>   a |  b   |  c
> ---+------+-----
>   6 | ccc  | BBB
>   4 | aaa  | BBB
>   1 | aaa  | AAA
>   2 | zxxx | AAA
>   3 | ccc  | ZZZ
>   5 | zxxx | BBB
> (6 rows)
>
> Alternatively: (a<>6),(a<>4),a

Although this does exactly what I want, at first glance it should do 
exactly the oposite.

I'm guessing that for each line it evaluates
not (a=6) 0 for true else 1
not (a=4) 0 for true else 1
everything else
-- 
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000     

Gary Stainburn <gary.stainburn@ringways.co.uk> writes:

> > Alternatively: (a<>6),(a<>4),a
> 
> Although this does exactly what I want, at first glance it should do 
> exactly the opposite.
> 
> I'm guessing that for each line it evaluates
> not (a=6) 0 for true else 1

Not really, "not a=6" is an expression that evaluates to a boolean, true or
false. true sorts as "greater" than false. That order is counterintuitive but
it's because the default sort order is ascending. So the "lesser" false
records appear first.

If you put "not a=6" in your select column list you'll see the true and false
values appear.


-- 
greg

Hi,

Let's suppose I have a plpgsql function like:

...
begin    alter ...      ...;    insert ...      ...;    create ...      ...;    drop ...;    and lot of such commands
inany order...
 
end;
...

(There is no "perform" keyword.)

Sometimes "ALTER" failes becouse it is already done. Sometimes 
"INSERT" failes becouse record already exists. Sometimes "DROP" 
failes bacouse object is already dropped by earlier execution of this 
function. When any of the command fails function has no effect at all 
becouse everything is rolled back.

Is it possible to run this function without rollback effect and just 
skip failures? (Server is 8.0.0-rc1.)

If the only way is putting each command in

begin    perform command;
exception    when others then        NULL;
end;

then I'm looking for a text processing tool which can do this 
"wrapping" automaticaly. I've too much functions and commands in them, 
so hand work is not an option.

Daniel