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

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?

3 answers

  • answered 2017-08-16 19:35 Kayaman

    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.

  • answered 2017-08-16 19:35 Robin

    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
    

  • answered 2017-08-16 19:35 dasblinkenlight

    If you are confident that there would be no duplicates, run a UNION ALL with 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_regex added to the result, which would contain string 'foo' or string 'bar'. You can use this column to decide in what "bucket" to put each row of the result.