Member Avatar for Rkeast

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
  • 123 Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by Rkeast
Member Avatar for Rkeast

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.