I need to select one and only 1 row of data based on an ID in the data I have. I thought I had solved this (For details, see my original question and my solution, here: PostgreSQL - Select only 1 row for each ID)
However, I now still get multiple values in some cases. If there is only "N/A" and 1 other value, then no problem.. but if I have multiple values like: "N/A", "value1" and "value2" for example, then my case statement is not sufficient and I get both "value1" and "value2" returned to me. This is the case statement in question:
CASE
WHEN "PQ"."Value" = 'N/A' THEN 1
ELSE 0
END
I need to give a unique integer value to each string value and then the problem will be solved. The question is: how do I do this? My first thought is to somehow convert the character values to ASCII and sum them up.. but I am not sure how to do that and also worried about performance. Is there a way to very simply assign a value to each string so that I can choose 1 value only? I don't care which one actually... just that it's only 1.
EDIT
I am now trying to create a function to add up the ASCII values of each character so I can essentially change my case statement to something like this:
CASE
WHEN "PQ"."Value" = 'N/A' THEN 9999999
ELSE SumASCII("PQ"."Value")
END
Having a small problem with it though.. I have added it as a separate question, here: PostgreSQL - ERROR: query has no destination for result data
EDIT 2
Thanks to @Bohemian, I now have a working solution, which is as follows:
CASE
WHEN "PQ"."Value" = 'N/A' THEN -1
ELSE ('x'||LPAD(MD5("PQ"."Value"),16,'0'))::bit(64)::bigint
END DESC
See Question&Answers more detail:os