Selecting Next Rows From Subquery Result

Question

It was easy to find a solution to get the following line with ONE id:

SELECT MIN(id) FROM foo WHERE id > ? 

But what if I wanted to get the following identifiers (plural) from the result of a subquery? For instance:.

 SELECT id FROM foo WHERE property > 0 

This query will return a certain number of identifiers, for example: 1, 2, 4, 6 . Suppose a row with id 3 has already been deleted, and the property of row 5 is <0.

So, I want the result to be as follows: 2, 4, 5, 7 .

The problem is that I cannot combine these two queries because a later version returns more than one row.

How can I get all subsequent identifiers from a subquery? Is this possible with a single request or will I need to use procedures?

Answer 1

If I read this right, you need a query that will work just like:

 SELECT MIN(id) FROM foo WHERE id > ? 

but for multiple identifiers. Thus, you get the next existing id in the sequence for a specific list of identifiers. It seems a little strange, I feel that the functionality you are trying to achieve may perhaps be implemented differently. I also assume that there is a good reason why you cannot just get the whole table and find the correct identifiers in any code that uses data.

Having said that, yes, it can be done. You already have a query that receives the correct "next" identifier, so you can join the table against yourself to create a table with two id columns, one of which is the original identifier, and the other is the "next". Then you can filter this table by the list of source identifiers and return another identifier. For instance:

 SELECT f1.id AS id FROM foo f1, foo f2 WHERE f1.id = ( SELECT MIN( f3.id ) FROM foo f3 WHERE f3.id > f2.id ) AND f2.id IN ( 1, 2, 4, 6 ) ; 

On my system, which returns 2, 4, 5, 7, if I set up the table as you describe (no identifiers 3, 5 and 7 have property <= 0).

This request may not be the fastest due to the large amount of data.

I think that what you want to do is because DeveloperCK says you can use GROUP_CONCAT if you need to pass the results of the property > 0 query to this query. But if this is what you want to do, you can skip the average person and combine the queries as follows:

 SELECT f1.id AS id FROM foo f1, foo f2 WHERE f1.id = ( SELECT MIN( f3.id ) FROM foo f3 WHERE f3.id > f2.id ) AND f2.property > 0 ; 

Answer 2

I still don’t quite understand your request, but I understand this feature will help you,

 GROUP_CONCAT() 

it will create a comma separated list for all rows for a specific column so you can get it with the first query. more details about this pls

visit: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

or

http://www.w3resource.com/mysql/aggregate-functions-and-grouping/aggregate-functions-and-grouping-group_concat.php