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....