Обсуждение: Order by provided IDs?

Hi.

If my query is:

SELECT art_id, art_headline, art_date
FROM article
WHERE art_id IN (357, 344, 120, 258, 369, 195, 343, 370);

can I ask Postgres to return the rows in the order of the IDs in the IN
clause? They are in a specific order defined outside the database.

Thanks,

Mark

Mark Kelly wrote on 14.07.2010 20:03:
> Hi.
>
> If my query is:
>
> SELECT art_id, art_headline, art_date
> FROM article
> WHERE art_id IN (357, 344, 120, 258, 369, 195, 343, 370);
>
> can I ask Postgres to return the rows in the order of the IDs in the IN
> clause? They are in a specific order defined outside the database.
>

Something like:

SELECT art_id, art_headline, art_date
FROM article a
  JOIN (values (1,357), (2,344), (3, 120), (4, 258), (5, 369), (6, 195), (7, 343), (8, 370) ) t(sort_order, id) ON t.id
=a.art_id 
ORDER BY t.sort_order

Hi.

On Wednesday 14 Jul 2010 at 19:22 Thomas Kellerer wrote:
> Mark Kelly wrote on 14.07.2010 20:03:
> > Hi.
> >
> > If my query is:
> >
> > SELECT art_id, art_headline, art_date
> > FROM article
> > WHERE art_id IN (357, 344, 120, 258, 369, 195, 343, 370);
> >
> > can I ask Postgres to return the rows in the order of the IDs in the IN
> > clause? They are in a specific order defined outside the database.
>
> Something like:
>
> SELECT art_id, art_headline, art_date
> FROM article a
>   JOIN (values (1,357), (2,344), (3, 120), (4, 258), (5, 369), (6, 195),
>  (7, 343), (8, 370) ) t(sort_order, id) ON t.id = a.art_id ORDER BY
>  t.sort_order

Ah, that's pretty cool - I didn't know I could do a join with anything other
than a table. I'll try it when I get back to work tomorrow.

Thanks for the reply,

Mark