0

Sorry for posting this in this forum, but there is no forum for PostGreSQL.

I am currently attempting to optimize a function that uses up to 3 seperate SELECT queries, by using a CASE conditional in my query.

Right now my function does an initial query, then if no results are found it does a second checking with different conditions, and if none is found on that it then does a third... This obviously needs some optimization.

The query I have so far is this:

SELECT *,
CASE 
WHEN product = 'Test' AND active = TRUE AND online = TRUE AND status = 'RUNNING' AND queued < 6 THEN 1
WHEN product = 'Test' AND active = TRUE AND online = TRUE AND status != 'DOWN' AND queued < 6 THEN 2
WHEN product = 'Test' AND active = TRUE AND online = TRUE AND status != 'DOWN' THEN 3
ELSE 4
END AS case_id
FROM blah ORDER BY queued ASC;

Now what I'd LIKE to do, is to take that case_id that it adds to each row from the case statement... and do something like this:

...
END AS case_id
FROM blah ORDER BY queued, case_id ASC LIMIT 1;

So that it will return 1 row, and that row will be the server with the optimum conditions from the CASE stuff... and will be the one with the lowest number queued...

But I can't figure out how I use that case_id value to actually DO something... or if I even can. I've spend an hour now looking online for a solution but I have hit a dead end....

1
Contributor
1
Reply
2
Views
8 Years
Discussion Span
Last Post by Rkeast
0

I actually did some more testing and found that if you do

...
END AS code_id
FROM blah ORDER BY code_id, queued ASC

It works correctly

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.