Find adjacent rows without stored procedure

Question

Considering the following table:

someId INTEGER #PK
ageStart TINYINT(3)
ageEnd TINYINT(3)
dateBegin INTEGER
dateEnd INTEGER

Where dateBegin and dateEnd are dates represented as days since 1800-12-28...

And considering some sample data:

someId | ageStart | ageEnd | dateStart | dateEnd
------------------------------------------------
   203 |       16 |     25 |     76533 |   76539   \
   506 |       16 |     25 |     76540 |   76546    adjacent rows
   384 |       16 |     25 |     76547 |   76553   /
   342 |       16 |     25 |     76563 |   76569   \
   545 |       16 |     25 |     76570 |   76576    adjacent rows
   764 |       16 |     25 |     76577 |   76583   /

(There would be arbitrary rows mixed in off course, I just want to illustrate 2 relevant rowsets)

Is it possible to find adjacent rows for a given age category (ageStart to ageEnd) without a stored procedure? The criteria for adjacency is: dateStart is 1 day after dateEnd of the previous found row.

For instance, given the above sample data, if I were to query it with the following parameters:

ageStart = 16
ageEnd = 25
dateStart = 76533

I would like it to return me the rows 1, 2 and 3 of the sample data, since their dates are adjacent (dayStart is next day of previous row's dateEnd).

ageStart = 16
ageEnd = 25
dateStart = 76563

...would give me rows 4, 5 and 6 of the sample data

Answer 1

Well, you can generate a result-set ordered in a specific way and use LIMIT, to get only first record from it.

For example, get the next record by dateEnd in the list:

SELECT *
FROM `table`
WHERE `dateEnd` > '76546'
ORDER BY `dateEnd`
LIMIT 1

You will get:

384 |       16 |     25 |     76547 |   76553

For a previous row:

SELECT *
FROM `table`
WHERE `dateEnd` < '76546'
ORDER BY `dateEnd` DESC
LIMIT 1

You will get:

   203 |       16 |     25 |     76533 |   76539

I doubt that it can be done with just one query...

Answer 2

You can consider your data to be in a parent-child relationship: a record is a child of a (parent) record if the child's startDate equals the parent's endDate + 1. For hierarchical data (with parent-child relationships), the nested sets model allows you to query the data without stored procedures. You can find a brief description of the nested sets model here:

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

The idea is to number your records in a clever way so that you can use simple queries instead of recursive stored procedures.

While it is very easy to query hierarchical data stored in this way, some care is required when adding new records. Adding new records in a nested sets model requires updates of existing records. This may or may not be acceptable in your use case.

Answer 3

Probably not efficient if lots of data into your table but try this:

SELECT b.*
FROM
(SELECT @continue:=2) init,
(
 SELECT * 
 FROM ageTable 
 WHERE ageStart=16 AND 
       ageEnd=25   AND 
       dateStart=76533
) a 
  INNER JOIN (
   SELECT *
   FROM ageTable
   ORDER BY dateStart
  ) b ON (
    b.ageStart=a.ageStart AND
    b.ageEnd=a.ageEnd     AND
    b.dateStart>=a.dateStart
  ) 
  LEFT JOIN ageTable c ON (
    c.dateStart=b.dateEnd+1 AND
    c.ageStart=b.ageStart   AND 
    c.ageEnd=b.ageEnd
  )
WHERE 
 CASE
  WHEN @continue=2 THEN
   CASE
    WHEN c.someId IS NULL THEN
     @continue:=1
    ELSE
     @continue
   END
  WHEN @continue=1 THEN
   @continue:=0
  ELSE
   @continue
 END