current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

I have the following postgres query:

SELECT array_agg("Esns".id ) 
FROM public."Esns", 
     public."PurchaseOrderItems" 
WHERE 
    "Esns"."PurchaseOrderItemId" = "PurchaseOrderItems".id 
    AND "PurchaseOrderItems"."GradeId"=2 
LIMIT 2;

The limit will affect the rows. I want it to limit the array_agg to 2 items. The following query works but i get my output with each entry in quotes:

SELECT array_agg ("temp")  
FROM (
    SELECT "Esns".id 
    FROM public."Esns", 
         public."PurchaseOrderItems" 
    WHERE 
        "Esns"."PurchaseOrderItemId" = "PurchaseOrderItems".id 
        AND "PurchaseOrderItems"."GradeId"=2 
    LIMIT 4
) as "temp" ;

This give me the following output "{(13),(14),(15),(12)}"

Any ideas?

share|improve this question

2 Answers 2

up vote 0 down vote accepted 
select id[1], id[2]
from (
    SELECT array_agg("Esns".id ) as id
    FROM public."Esns", 
         public."PurchaseOrderItems" 
    WHERE 
        "Esns"."PurchaseOrderItemId" = "PurchaseOrderItems".id 
        AND "PurchaseOrderItems"."GradeId"=2 
) s

or if you want the output as array:

SELECT (array_agg("Esns".id ))[1:2] as id_array
FROM public."Esns", 
     public."PurchaseOrderItems" 
WHERE 
    "Esns"."PurchaseOrderItemId" = "PurchaseOrderItems".id 
    AND "PurchaseOrderItems"."GradeId"=2
share|improve this answer
    
Perfect. Thanks. The second answer is what i was looking for as i do want an array and i want to be able to dynamically set the upper bound on the limit. Thank you. –  user1175817 Sep 19 '12 at 16:01
up vote 0 down vote

Direct array construction from a query result should be simpler and faster:

SELECT ARRAY(
   SELECT "Esns".id 
   FROM   public."Esns" e
   JOIN   public."PurchaseOrderItems" p ON p.id = e."PurchaseOrderItemId"
   WHERE  p."GradeId" = 2 
   --  ORDER BY ???
   LIMIT  4  -- or 2 ?
   )

You need to ORDER BY something if you want a stable result and / or pick certain rows. Otherwise the result is arbitrary and can change at any time.

While being at it I rewrote the query with explicit JOIN syntax, which is generally preferable, and used aliases to simplify.

share|improve this answer

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.