JDBC - one query to return separate groups of results
I have a table that looks like this:
Col: A (string), B (int), C(string)
I want to be able to get rows where
A matches either regex
foo or regex
bar. Currently, I perform two queries:
SELECT * FROM myTable WHERE A LIKE foo and
SELECT * FROM myTable WHERE A LIKE bar
I am confident that there will be no duplicates between these result sets.
I would like to be able to get an equivalent result by running a single query, so that I don't have to scan the table once for each acceptable value, for example:
SELECT * FROM myTable WHERE A LIKE foo OR A LIKE bar
My problem is that such a query produces a result set with all the values jumbled together - I would then need to loop through using my own string comparison to separate the two. Furthermore, strings that match
A LIKE foo can be very different, so I can't just group on
Is there a way to perform a single traversal of the table (for efficiency's sake), but still get JDBC / SQL to do the work of separating the results into one group for
A LIKE foo and another group for
A LIKE bar?
You could add an additional boolean column to the resultset and even sort by that.
SELECT A LIKE foo AS group_foo, * FROM myTable WHERE A LIKE foo OR A LIKE bar ORDER BY 1;
The first column of the resultset then tells which group it belongs to (true for foo, false for bar) and it's easy to separate them when processing the resultset.
You can use the query below, add order by column A
SELECT * FROM myTable WHERE A LIKE foo OR A LIKE bar order by A
If you are confident that there would be no duplicates, run a
UNION ALLwith a discriminator:
SELECT 'foo' as which_regex, * FROM myTable WHERE A LIKE foo UNION ALL SELECT 'bar' as which_regex, * FROM myTable WHERE A LIKE bar
JDBC will run this as a single query, even though it has two parts. There will be an extra "discriminator" column called
which_regexadded to the result, which would contain string
'bar'. You can use this column to decide in what "bucket" to put each row of the result.