Обсуждение: Ordering Results by a Supplied Order
Greetings,
+-----+----------+--------+
| rid | position | name |
+-----+----------+--------+
| 1 | 1 | Apple |
| 2 | 2 | Orange |
| 3 | 3 | Pear |
+-----+----------+--------+
-- Now, lets return the results ordered by the our user-supplied order
Often times I find it necessary to work with table rows in a specific, generally user-supplied order. It could be anything really that requires an ordering that can't come from a natural column. Most of the time this involved manipulating a position column from the client application. In any case, I've often found that to be cumbersome, but I think I've come up with a solution that some of you may find useful.
Regards,
Michael
-- Lets start by creating a new table.
CREATE TABLE my_items (
"rid" serial NOT NULL,
"position" int4 NOT NULL,
"name" text NOT NULL,
PRIMARY KEY ("rid")
);
INSERT INTO my_items (position, name) VALUES (1, 'Apple');
INSERT INTO my_items (position, name) VALUES (2, 'Orange');
INSERT INTO my_items (position, name) VALUES (3, 'Pear');
select * from my_items;
"rid" serial NOT NULL,
"position" int4 NOT NULL,
"name" text NOT NULL,
PRIMARY KEY ("rid")
);
INSERT INTO my_items (position, name) VALUES (1, 'Apple');
INSERT INTO my_items (position, name) VALUES (2, 'Orange');
INSERT INTO my_items (position, name) VALUES (3, 'Pear');
select * from my_items;
+-----+----------+--------+
| rid | position | name |
+-----+----------+--------+
| 1 | 1 | Apple |
| 2 | 2 | Orange |
| 3 | 3 | Pear |
+-----+----------+--------+
-- Now, lets return the results ordered by the our user-supplied order
-- ARRAY[3,2,1] is the key here where 3,2,1 represent the pk's in the order we wish
with x as (
select position, (ARRAY[3,2,1])[position] rid from generate_series(1, 3) as series(position) order by position asc
)
select item.* from x join my_items item on (item.rid=x.rid);
CREATE FUNCTION "public"."set_item_order"(IN items _int4) RETURNS "bool" AS
$BODY$
with x as(
select
with x as (
select position, (ARRAY[3,2,1])[position] rid from generate_series(1, 3) as series(position) order by position asc
)
select item.* from x join my_items item on (item.rid=x.rid);
+-----+----------+--------+
| rid | position | name |
+-----+----------+--------+
| 3 | 3 | Pear |
| 2 | 2 | Orange |
| 1 | 1 | Apple |
+-----+----------+--------+
-- Better yet, lets create a function that will update the position column.
CREATE FUNCTION "public"."set_item_order"(IN items _int4) RETURNS "bool" AS
$BODY$
with x as(
select
position,
($1)[position] as rid
from generate_series(1,array_length($1, 1)) as series(position)
order by position asc
)
update my_items set position=x.position FROM x where x.rid=my_items.rid;
select TRUE;
$BODY$
LANGUAGE sql
COST 100
CALLED ON NULL INPUT
SECURITY INVOKER
VOLATILE;
select set_item_order(ARRAY[3,2,1]);
select * from my_items order by position;
+-----+----------+--------+
| rid | position | name |
+-----+----------+--------+
| 3 | 1 | Pear |
| 2 | 2 | Orange |
| 1 | 3 | Apple |
+-----+----------+--------+
)
update my_items set position=x.position FROM x where x.rid=my_items.rid;
select TRUE;
$BODY$
LANGUAGE sql
COST 100
CALLED ON NULL INPUT
SECURITY INVOKER
VOLATILE;
select set_item_order(ARRAY[3,2,1]);
select * from my_items order by position;
+-----+----------+--------+
| rid | position | name |
+-----+----------+--------+
| 3 | 1 | Pear |
| 2 | 2 | Orange |
| 1 | 3 | Apple |
+-----+----------+--------+
On 02/06/2014 04:16 AM, Michael Sacket wrote:
Often times I find it necessary to work with table rows in a specific, generally user-supplied order. It could be anything really that requires an ordering that can't come from a natural column. Most of the time this involved manipulating a position column from the client application. In any case, I've often found that to be cumbersome, but I think I've come up with a solution that some of you may find useful.
Up until 9.4, that's a good way to do it.
Starting from 9.4, you can use the WITH ORDINALITY feature.
http://www.postgresql.org/docs/devel/static/sql-select.html
-- Vik
Hey,
I don"t understand the difference between this ORDINALITY option and adding a "row_number() over()" in the SELECT.Remi-C
2014-02-06 Vik Fearing <vik.fearing@dalibo.com>:
Up until 9.4, that's a good way to do it.On 02/06/2014 04:16 AM, Michael Sacket wrote:Often times I find it necessary to work with table rows in a specific, generally user-supplied order. It could be anything really that requires an ordering that can't come from a natural column. Most of the time this involved manipulating a position column from the client application. In any case, I've often found that to be cumbersome, but I think I've come up with a solution that some of you may find useful.
Starting from 9.4, you can use the WITH ORDINALITY feature.
http://www.postgresql.org/docs/devel/static/sql-select.html-- Vik
On 02/06/2014 10:00 AM, Rémi Cura wrote: > Hey, > I don"t understand the difference between this ORDINALITY option and > adding a "row_number() over()" in the SELECT. WITH ORDINALITY will give you something to order by. You should never do "row_number() over ()" because that will give you potentially random results. -- Vik
Ok, thanks ^^
Cheers,Rémi-C
2014-02-06 Vik Fearing <vik.fearing@dalibo.com>:
On 02/06/2014 10:00 AM, Rémi Cura wrote:WITH ORDINALITY will give you something to order by. You should never
> Hey,
> I don"t understand the difference between this ORDINALITY option and
> adding a "row_number() over()" in the SELECT.
do "row_number() over ()" because that will give you potentially random
results.
--
Vik
On Wed, Feb 5, 2014 at 7:16 PM, Michael Sacket <msacket@gammastream.com> wrote: > Often times I find it necessary to work with table rows in a specific, > generally user-supplied order. It could be anything really that requires an > ordering that can't come from a natural column. Most of the time this > involved manipulating a position column from the client application. In any > case, I've often found that to be cumbersome, but I think I've come up with > a solution that some of you may find useful. There also are some ways of doing it without creating an additional column http://www.depesz.com/2010/07/25/how-to-order-by-some-random-query-defined-values/. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray.ru@gmail.com
On Feb 6, 2014, at 2:23 AM, Vik Fearing wrote: > On 02/06/2014 04:16 AM, Michael Sacket wrote: >> Often times I find it necessary to work with table rows in a specific, generally user-supplied order. It could be anythingreally that requires an ordering that can't come from a natural column. Most of the time this involved manipulatinga position column from the client application. In any case, I've often found that to be cumbersome, but I thinkI've come up with a solution that some of you may find useful. >> > > Up until 9.4, that's a good way to do it. > > Starting from 9.4, you can use the WITH ORDINALITY feature. > http://www.postgresql.org/docs/devel/static/sql-select.html > -- > Vik Even better! The development team is always making my work easier in unexpected ways. Thanks!
On 07/02/14 05:43, Michael Sacket wrote: > On Feb 6, 2014, at 2:23 AM, Vik Fearing wrote: > >> On 02/06/2014 04:16 AM, Michael Sacket wrote: >>> Often times I find it necessary to work with table rows in a specific, generally user-supplied order. It could be anythingreally that requires an ordering that can't come from a natural column. Most of the time this involved manipulatinga position column from the client application. In any case, I've often found that to be cumbersome, but I thinkI've come up with a solution that some of you may find useful. >>> >> Up until 9.4, that's a good way to do it. >> >> Starting from 9.4, you can use the WITH ORDINALITY feature. >> http://www.postgresql.org/docs/devel/static/sql-select.html >> -- >> Vik > Even better! The development team is always making my work easier in unexpected ways. > > Thanks! > > You do realize, that with this new feature, the licence fee for PostgreSQL will dramatically increase? :-) Cheers, Gavin
On 02/06/2014 11:57 AM, Gavin Flower wrote:
On 07/02/14 05:43, Michael Sacket wrote:A ten-fold increase at least!On Feb 6, 2014, at 2:23 AM, Vik Fearing wrote:You do realize, that with this new feature, the licence fee for PostgreSQL will dramatically increase? :-)On 02/06/2014 04:16 AM, Michael Sacket wrote:Even better! The development team is always making my work easier in unexpected ways.Often times I find it necessary to work with table rows in a specific, generally user-supplied order. It could be anything really that requires an ordering that can't come from a natural column. Most of the time this involved manipulating a position column from the client application. In any case, I've often found that to be cumbersome, but I think I've come up with a solution that some of you may find useful.Up until 9.4, that's a good way to do it.
Starting from 9.4, you can use the WITH ORDINALITY feature.
http://www.postgresql.org/docs/devel/static/sql-select.html
--
Vik
Thanks!
Cheers,
Gavin
On Feb 6, 2014, at 12:57 PM, Gavin Flower wrote: > On 07/02/14 05:43, Michael Sacket wrote: >> On Feb 6, 2014, at 2:23 AM, Vik Fearing wrote: >> >>> On 02/06/2014 04:16 AM, Michael Sacket wrote: >>>> Often times I find it necessary to work with table rows in a specific, generally user-supplied order. It could be anythingreally that requires an ordering that can't come from a natural column. Most of the time this involved manipulatinga position column from the client application. In any case, I've often found that to be cumbersome, but I thinkI've come up with a solution that some of you may find useful. >>>> >>> Up until 9.4, that's a good way to do it. >>> >>> Starting from 9.4, you can use the WITH ORDINALITY feature. >>> http://www.postgresql.org/docs/devel/static/sql-select.html >>> -- >>> Vik >> Even better! The development team is always making my work easier in unexpected ways. >> >> Thanks! >> >> > You do realize, that with this new feature, the licence fee for PostgreSQL will dramatically increase? :-) > > > Cheers, > Gavin Nope, I missed that. Still says free as far as I can find. :-) I did however find a donate button. I encourage othersto find it too!