IN 값 목록으로 주문

IN 값 목록으로 주문

---

PostgreSQL 8.3에는 많은 주석을 얻는 간단한 SQL 쿼리가 있습니다. 나는 제공 정렬 받는 값 목록 IN의 구조 WHERE절을 :

SELECT * FROM comments WHERE (comments.id IN (1,3,2,4));

이것은 내에서 id와 같은 임의의 순서로 주석을 반환합니다 1,2,3,4.

결과 행이 IN구문 의 목록과 같이 정렬되기를 원합니다 (1,3,2,4).
그것을 달성하는 방법?

---

(PostgreSQL 8.2에서 도입) VALUES (), ()로 아주 쉽게 할 수 있습니다.

구문은 다음과 같습니다.

select c.*
from comments c
join (
  values
    (1,1),
    (3,2),
    (2,3),
    (4,4)
) as x (id, ordering) on c.id = x.id
order by x.ordering

---

찾기가 너무 어렵고 확산되어야하기 때문에 mySQL에서는 훨씬 간단하게 수행 할 수 있지만 다른 SQL에서 작동하는지는 알 수 없습니다.

SELECT * FROM `comments`
WHERE `comments`.`id` IN ('12','5','3','17')
ORDER BY FIELD(`comments`.`id`,'12','5','3','17')

---

나는이 방법이 더 낫다고 생각합니다.

SELECT * FROM "comments" WHERE ("comments"."id" IN (1,3,2,4))
    ORDER BY  id=1 DESC, id=3 DESC, id=2 DESC, id=4 DESC

---

Postgres 9.4 이상 에서는 아마도 가장 간단하고 빠릅니다 .

SELECT c.*
FROM   comments c
JOIN   unnest('{1,3,2,4}'::int[]) WITH ORDINALITY t(id, ord) USING (id)
ORDER  BY t.ord;

• @a_horse 는 새로운 WITH ORDINALITY것을 사용하여 이미 언급했다 .

• 하위 쿼리가 필요하지 않으며 테이블처럼 set-returning 함수를 사용할 수 있습니다.

• ARRAY 생성자 대신 배열에 전달할 문자열 리터럴 은 일부 클라이언트에서 구현하기가 더 쉬울 수 있습니다.

상해:

• 요소 번호가있는 PostgreSQL unnest ()

---

Postgres 9.4를 사용하면 조금 더 짧게 수행 할 수 있습니다.

select c.*
from comments c
join (
  select *
  from unnest(array[43,47,42]) with ordinality
) as x (id, ordering) on c.id = x.id
order by x.ordering

각 값에 위치를 수동으로 할당 / 유지할 필요가 없습니다.

로 포스트 그레스 9.6 이 사용하여 수행 할 수 있습니다 array_position():

with x (id_list) as (
  values (array[42,48,43])
)
select c.*
from comments c, x
where id = any (x.id_list)
order by array_position(x.id_list, c.id);

The CTE is used so that the list of values only needs to be specified once. If that is not important this can also be written as:

select c.*
from comments c
where id in (42,48,43)
order by array_position(array[42,48,43], c.id);

---

Another way to do it in Postgres would be to use the idx function.

SELECT *
FROM comments
ORDER BY idx(array[1,3,2,4], comments.id)

Don't forget to create the idx function first, as described here: http://wiki.postgresql.org/wiki/Array_Index

---

In Postgresql:

select *
from comments
where id in (1,3,2,4)
order by position(id::text in '1,3,2,4')

---

On researching this some more I found this solution:

SELECT * FROM "comments" WHERE ("comments"."id" IN (1,3,2,4)) 
ORDER BY CASE "comments"."id"
WHEN 1 THEN 1
WHEN 3 THEN 2
WHEN 2 THEN 3
WHEN 4 THEN 4
END

However this seems rather verbose and might have performance issues with large datasets. Can anyone comment on these issues?

---

To do this, I think you should probably have an additional "ORDER" table which defines the mapping of IDs to order (effectively doing what your response to your own question said), which you can then use as an additional column on your select which you can then sort on.

In that way, you explicitly describe the ordering you desire in the database, where it should be.

---

sans SEQUENCE, works only on 8.4:

select * from comments c
join 
(
    select id, row_number() over() as id_sorter  
    from (select unnest(ARRAY[1,3,2,4]) as id) as y
) x on x.id = c.id
order by x.id_sorter

---

SELECT * FROM "comments" JOIN (
  SELECT 1 as "id",1 as "order" UNION ALL 
  SELECT 3,2 UNION ALL SELECT 2,3 UNION ALL SELECT 4,4
) j ON "comments"."id" = j."id" ORDER BY j.ORDER

or if you prefer evil over good:

SELECT * FROM "comments" WHERE ("comments"."id" IN (1,3,2,4))
ORDER BY POSITION(','+"comments"."id"+',' IN ',1,3,2,4,')

---

I agree with all other posters that say "don't do that" or "SQL isn't good at that". If you want to sort by some facet of comments then add another integer column to one of your tables to hold your sort criteria and sort by that value. eg "ORDER BY comments.sort DESC " If you want to sort these in a different order every time then... SQL won't be for you in this case.

---

And here's another solution that works and uses a constant table (http://www.postgresql.org/docs/8.3/interactive/sql-values.html):

SELECT * FROM comments AS c,
(VALUES (1,1),(3,2),(2,3),(4,4) ) AS t (ord_id,ord)
WHERE (c.id IN (1,3,2,4)) AND (c.id = t.ord_id)
ORDER BY ord

But again I'm not sure that this is performant.

I've got a bunch of answers now. Can I get some voting and comments so I know which is the winner!

Thanks All :-)

---

create sequence serial start 1;

select * from comments c
join (select unnest(ARRAY[1,3,2,4]) as id, nextval('serial') as id_sorter) x
on x.id = c.id
order by x.id_sorter;

drop sequence serial;

[EDIT]

unnest is not yet built-in in 8.3, but you can create one yourself(the beauty of any*):

create function unnest(anyarray) returns setof anyelement
language sql as
$$
    select $1[i] from generate_series(array_lower($1,1),array_upper($1,1)) i;
$$;

that function can work in any type:

select unnest(array['John','Paul','George','Ringo']) as beatle
select unnest(array[1,3,2,4]) as id

---

Slight improvement over the version that uses a sequence I think:

CREATE OR REPLACE FUNCTION in_sort(anyarray, out id anyelement, out ordinal int)
LANGUAGE SQL AS
$$
    SELECT $1[i], i FROM generate_series(array_lower($1,1),array_upper($1,1)) i;
$$;

SELECT 
    * 
FROM 
    comments c
    INNER JOIN (SELECT * FROM in_sort(ARRAY[1,3,2,4])) AS in_sort
        USING (id)
ORDER BY in_sort.ordinal;

---

select * from comments where comments.id in 
(select unnest(ids) from bbs where id=19795) 
order by array_position((select ids from bbs where id=19795),comments.id)

here, [bbs] is the main table that has a field called ids, and, ids is the array that store the comments.id .

passed in postgresql 9.6

---

Lets get a visual impression about what was already said. For example you have a table with some tasks:

SELECT a.id,a.status,a.description FROM minicloud_tasks as a ORDER BY random();

 id |   status   |   description    
----+------------+------------------
  4 | processing | work on postgres
  6 | deleted    | need some rest
  3 | pending    | garden party
  5 | completed  | work on html

And you want to order the list of tasks by its status. The status is a list of string values:

(processing, pending,  completed, deleted)

The trick is to give each status value an interger and order the list numerical:

SELECT a.id,a.status,a.description FROM minicloud_tasks AS a
  JOIN (
    VALUES ('processing', 1), ('pending', 2), ('completed', 3), ('deleted', 4)
  ) AS b (status, id) ON (a.status = b.status)
  ORDER BY b.id ASC;

Which leads to:

 id |   status   |   description    
----+------------+------------------
  4 | processing | work on postgres
  3 | pending    | garden party
  5 | completed  | work on html
  6 | deleted    | need some rest

Credit @user80168

참고URL : https://stackoverflow.com/questions/866465/order-by-the-in-value-list