CODEWITHSUNDEEP
mysqlsqlarraysloopsjoin
Anton Dozortsev
Anton Dozortsev

Reputation: 4892

Iterate result set like array

How I can in MySQL fetch any row by index from result set as it possible with arrays or collections in most programming languages ?

array[index]

Or:

collection.getElementByIndex(index)

Update:

I have a result set of dates, me need to check whether the 90 days between each date

Upvotes: 1

Views: 87

Answers (2)

Mats Kindahl
Mats Kindahl

Reputation: 2075

You have two alternatives:

  1. Use a a sub-select.
  2. Use the ability for MySQL to iterate over the returned rows.

First alternative looks like:

SELECT BIT_AND(IFNULL(DATEDIFF((SELECT dt FROM foo WHERE dt > a.dt ORDER BY dt LIMIT 1), a.dt) >= 90, 1)) AS all_larger
  FROM foo a;

Update: To handle a table where a date is duplicated, it is necessary to add a second sub-select to see if there are duplicates for the date, as follows:

SELECT BIT_AND(larger && ! duplicates) AS all_larger
  FROM (SELECT a.dt
             , IFNULL(DATEDIFF((SELECT dt FROM foo WHERE dt > a.dt ORDER BY dt LIMIT 1), a.dt) >= 90, 1) AS larger
             , (SELECT COUNT(*) FROM foo WHERE dt = a.dt) > 1 AS duplicates
          FROM foo a) AS x;

Second alternative looks like:

SET @prev = NULL;
SELECT BIT_AND(a.larger) AS all_larger
  FROM (SELECT IFNULL(DATEDIFF(dt, @prev) >= 90, 1) AS larger
             , @prev := dt
          FROM foo ORDER BY dt) a;

Both give the following result set when run on a table where the difference between the dates are more than 90 days:

+------------+
| all_larger |
+------------+
|          1 |
+------------+

The second one is probably faster, but I haven't measured on larger sets.

Upvotes: 1

Scott
Scott

Reputation: 3732

Intrinsically you cannot. A relational database doesn't preserve record order (or at least you can't rely on it, even if it temporarily stores record order). In this way it acts more like a hashmap or List than an array.

However if you want, you can add a field in the table - let's call it RowNum - that stores a row number, and you can query on that.

select * from Table where RowNum = %index%;

Upvotes: 0

Related Questions