SELECT id
FROM my_table
WHERE id IN (
SELECT id
FROM my_table
WHERE criteria_a = 19
ORDER BY create_when DESC
LIMIT 1000
);
This is the pattern I am looking for, but I need the criteria_a to be repeated for every value of criteria_a with the important focus being the LIMIT 1000 for any single value of criteria_a. There is no need to put a total LIMIT on the query, just to limit to the 1000 per criteria_a with the specific ORDER BY at that point. Put another way…
SELECT id
FROM my_table
WHERE id IN (
SELECT id
FROM my_table
WHERE criteria_a = 19
ORDER BY create_when DESC
LIMIT 1000
)
OR id IN (
SELECT id
FROM my_table
WHERE criteria_a = 20
ORDER BY create_when DESC
LIMIT 1000
);
Where I desire 2000 total rows. I could turn this into programming code (even a PostgreSQL FUNCTION) that loops over every value of criteria_a and replaces 19 in the example.
I don’t care of it is a JOIN or an IN, I’m more stuck on how to repeat the inner SELECT with the LIMIT 1000 based on sort and criteria_a. Can I do it without looping and/or UNION? Thank you.
Glad this is working for you. Using TOP probably was a bad idea and I think the way you used RANK <=1000 is a better approach.
If there was a way to safely exclude any of the records - like if you knew that when published was older than X days/months/years it would never make it into the final results, you could filter them out before ranking them. That might squeeze a little more performance out of the query, but could be risky if the data isn’t predictable enough.
Thank you