Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 4 down vote favorite
2

I have two tables in database:

CREATE TABLE items(
 id SERIAL PRIMARy KEY,
 ... some other fields
);

This table contains come data row with unique ID.

CREATE TABLE some_chosen_data_in_order(
 id SERIAL PRIMARy KEY,
 id_items INTEGER[],

);

This table contains array type field. Each row contains values of IDs from table "items" in specyfic order. For example: {2,4,233,5}.

Now, I want to get data from table "items" for chosen row from table "some_chosen_data_in_order" with order for elements in array type.

The my attempt is JOIN:

SELECT I.* FROM items AS I 
JOIN some_chosen_data_in_order AS S ON I.id = ANY(S.id_items) WHERE S.id = ?

Second attempt was subquery like:

SELECT I.* FROM items AS I 
WHERE I.id = ANY 
(ARRAY[SELECT S.id_items FROM some_chosen_data_in_order  WHERE id = ?])

But none of them keep IDs order in array field. Could You help me, how to get data from "items" table with correspond with array IDs order from "some_chosen_data_in_order" table for specyfic row?

share|improve this question

2 Answers

up vote 4 down vote accepted

Probably normalizing your table would be the best advice I can give you.

The int_array contrib module has an idx function that will give you the int's index position in the array. Also there is an idx function on the snippets wiki that works for array's of any data types.

SELECT i.*, idx(id_items, i.id) AS idx
FROM some_chosen_data_in_order s
JOIN items i ON i.id = ANY(s.id_items)
ORDER BY idx(id_items, i.id)
share|improve this answer
It's clearly what I need. Thanx! I'm denormalizing my database due to efficiency reasons. – Adiasz Mar 24 '10 at 18:41
up vote 3 down vote 
SELECT t.*
FROM unnest(ARRAY[1,2,3,2,3,5]) item_id
LEFT JOIN items t on t.id=item_id

The above query select items from items table with ids 1,2,3,2,3,5 in that order. Is that what You mean? :)

share|improve this answer
unnest() is a really cool trick! I had a list of ID's from a customer and needed to join against them but wanted to find a way to do it w/o a temp table. You can join (select unnest(ARRAY[1,2,3])) as idlist and it works like a champ. thanks! – apinstein Apr 3 at 2:11
@apinstein can you please show me the query? still dint get it – manocha_ak Apr 10 at 5:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.