SQL loop through an array and run a query multiple times

Question

I have an array with some status ids

[1,2,3,4,5]

I want to run this query

SELECT firstname, lastname FROM calls
WHERE status_id = "STATUS ID"
ORDER BY RANDOM() LIMIT 50

What would the syntax be to do a loop though the status_id array and run the query each time?

I want to end up with 250 rows with each group of 50 having a one of the status_id's.

Do you want all of them at once or separate result sets? Also, which RDBMS are you using. I doubt it's both mysql and postgresql. — Eric Hauenstein
– Eric Hauenstein, Commented Jun 16, 2015 at 13:34
Hey, in postgres please i've adjusted the query btw missed a bit off — Jake McAllister
– Jake McAllister, Commented Jun 16, 2015 at 13:37
status_id = "STATUS ID"? In the first part of the question, status_id appears to be an integer. And why order by RANDOM()? If you don't need them in order, just take them as they come. — Eric Hauenstein
– Eric Hauenstein, Commented Jun 16, 2015 at 13:38
Yeah it is an integer just doing that to represent thats where the status id is — Jake McAllister
– Jake McAllister, Commented Jun 16, 2015 at 13:40
i added this bit to the query ORDER BY RANDOM() LIMIT 50 And this bit What i want to end up with is 250 rows with each group of 50 having a one of the status id's — Jake McAllister
– Jake McAllister, Commented Jun 16, 2015 at 13:40

score 1 · Accepted Answer · 2015-06-25 08:44:47Z

1

This won't be terribly efficient, but it's the best I can think of:

select firstname, lastname
FROM (
  SELECT firstname, 
         lastname, 
         row_number() over (partition by status_id order by random()) as rn
  FROM calls 
  WHERE status_id = ANY (ARRAY [1,2,3,4,5])
) t
where rn <= 50;

The inner select will retrieve all rows with the desired values for status_id and will give each row a random row number for each value in status_id. The outer select will then only select 50 rows for each status (unless there are less than 50 for that specific status of course)

edited Jun 25, 2015 at 8:44

answered Jun 16, 2015 at 13:48

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

Erwin Brandstetter Over a year ago

ORDER BY status_id

Ramki · Accepted Answer · 2015-06-16 13:32:46Z

1

Use In operator:

 SELECT firstname, lastname FROM calls WHERE status_id IN [1,2,3,4,5]

answered Jun 16, 2015 at 13:32

Ramki

5192 silver badges9 bronze badges

2 Comments

Jake McAllister Over a year ago

I've adjusted the question how would you do it now?

Dan Bracuk Over a year ago

I think you need round brackets.

Collectives™ on Stack Overflow

SQL loop through an array and run a query multiple times

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related