# MySQL contiguous records

**URL:** https://forum.kirupa.com/t/mysql-contiguous-records/261559
**Category:** programming
**Created:** [May 28, 2008, 9:03pm UTC](https://forum.kirupa.com/t/mysql-contiguous-records/261559 "2008-05-28T21:03:11Z")
**Posts on this page:** 19
**Page:** 1

<div class="post-metadata">

### Author: ![imagined](https://avatars.discourse-cdn.com/v4/letter/i/a87d85/32.png) [@imagined](https://forum.kirupa.com/u/imagined)
#### Post date: [May 28, 2008, 9:03pm UTC](https://forum.kirupa.com/t/mysql-contiguous-records/261559/1 "2008-05-28T21:03:11Z")

</div>

Let’s say I have this query:

SELECT id, name, datecreated FROM mytable WHERE id = '.$\_GET[‘id’]

I want to get 2 records before that and 2 after that, but the thing is, they have to be sorted by datecreated

any ideas?

---

<div class="post-metadata">

### Author: ![kdd](https://avatars.discourse-cdn.com/v4/letter/k/ecb155/32.png) [@kdd](https://forum.kirupa.com/u/kdd)
#### Post date: [May 29, 2008, 12:03am UTC](https://forum.kirupa.com/t/mysql-contiguous-records/261559/2 "2008-05-29T00:03:03Z")

</div>

Well, there are a couple of ways to do this…

1. use 2 queries
2. here’s the solution:

```php

$id = $_get['id']; //user input, so beware, and check it!
select ... from ... where id='$id-1' or id='$id-2' or id='$id+1' or id='$id+2' order by datecreated desc

```

🙂

---

<div class="post-metadata">

### Author: ![djheru](https://yyz1.discourse-cdn.com/flex011/user_avatar/forum.kirupa.com/djheru/32/3190_2.png) [@djheru](https://forum.kirupa.com/u/djheru)
#### Post date: [May 29, 2008, 5:34am UTC](https://forum.kirupa.com/t/mysql-contiguous-records/261559/3 "2008-05-29T05:34:48Z")

</div>

that is a good brain teaser. This is what I came up with:

```php

$id = $_GET['id'];
$query = <<<EOT
SELECT id, name, datecreated FROM mytable
WHERE
     (datecreated >=
          (SELECT datecreated FROM mytable WHERE id = $id)
     LIMIT 3)
OR
     (datecreated <
          (SELECT datecreated FROM mytable WHERE id = $id)
     LIMIT 2)
EOT;

```

Please let me know if it works.

---

<div class="post-metadata">

### Author: ![kdd](https://avatars.discourse-cdn.com/v4/letter/k/ecb155/32.png) [@kdd](https://forum.kirupa.com/u/kdd)
#### Post date: [May 29, 2008, 6:24am UTC](https://forum.kirupa.com/t/mysql-contiguous-records/261559/4 "2008-05-29T06:24:07Z")

</div>

Even I should say Thank You to djheru! 🙂 Not sure if it works, but your way is more general, whereas mine was more concrete to the problem given. (Don’t know if “concrete” word is correct, but you get the point, mine was not general way).

---

<div class="post-metadata">

### Author: ![agnus](https://avatars.discourse-cdn.com/v4/letter/a/4bbf92/32.png) [@agnus](https://forum.kirupa.com/u/agnus)
#### Post date: [May 29, 2008, 9:17am UTC](https://forum.kirupa.com/t/mysql-contiguous-records/261559/5 "2008-05-29T09:17:29Z")

</div>

Actually, there is a simpler way:

```php

$start_id = (int)$_GET['id'] - 2;
$end_id = (int)$_GET['id'] + 2;

$sql = "SELECT * FROM my_table WHERE id BETWEEN $start_id AND $end_id";
mysql_query($sql).....and so on

```

---

<div class="post-metadata">

### Author: ![agnus](https://avatars.discourse-cdn.com/v4/letter/a/4bbf92/32.png) [@agnus](https://forum.kirupa.com/u/agnus)
#### Post date: [May 29, 2008, 9:38am UTC](https://forum.kirupa.com/t/mysql-contiguous-records/261559/6 "2008-05-29T09:38:25Z")

</div>

Well, if ID isn’t a number, you could try making a select to determine the id of the row and then run the SQL mentioned above. It all depends on what data you have and what you need to accomplish.  
Oh, and btw, always remember to sanitize input data.

---

<div class="post-metadata">

### Author: ![Charleh](https://avatars.discourse-cdn.com/v4/letter/c/a9a28c/32.png) [@Charleh](https://forum.kirupa.com/u/Charleh)
#### Post date: [May 29, 2008, 1:30pm UTC](https://forum.kirupa.com/t/mysql-contiguous-records/261559/7 "2008-05-29T13:30:39Z")

</div>

You should use the LIMIT and possibly OFFSET parameters if the ID is not numeric - also bear in mind that although IDs may be numeric they are not necessarily contiguous because data may have been removed so therefore djherus solution is more elegant in both cases and works even when data has been removed - this is the one to use

---

<div class="post-metadata">

### Author: ![imagined](https://avatars.discourse-cdn.com/v4/letter/i/a87d85/32.png) [@imagined](https://forum.kirupa.com/u/imagined)
#### Post date: [May 29, 2008, 2:10pm UTC](https://forum.kirupa.com/t/mysql-contiguous-records/261559/8 "2008-05-29T14:10:54Z")

</div>

thanks, those are all good suggestions.

this is for a photo gallery, but what if a user deletes photo with id = 65? then, if this system is based purely on id’s it wont work, because what if someone wants to see photo 64?

this is part of what i came up with, but it’s based purely on id

```php

// QUERY TO SELECT UNIQUE RECORD
$query = 'SELECT '.$displayable.' FROM '.$tablename.' WHERE id = '.$_GET['id'];
$result = mysql_db_query($db_name, $query);
$row = mysql_fetch_array($result);

//////////////////////////////////////
// SELECT CONTIGUOUS RECORDS
////////////////////////////////////
$more = $_GET['id'] - 2;
$less = $_GET['id'] + 2;
$whereNearby = 'WHERE id >= '.$more.' AND id <= '.$less;

if(isset($_GET['categoryid'])&&is_numeric($_GET['categoryid']))
{
	$whereNearby .= ' AND (category = "'.$_GET['categoryid'].'" OR category2 = "'.$_GET['categoryid'].'")';
	// Query to display items from a specific category
	$query = 'SELECT '.$displayable.' FROM '.$tablename.' '.$where.' ORDER BY datecreated DESC '.$limit;
}

$queryNearby = 'SELECT '.$displayable.' FROM '.$tablename.' '.$whereNearby.' ORDER BY datecreated DESC';
$resultNearby = mysql_query($queryNearby);

$return .= '<table><tr>';		
while($rowNearby = mysql_fetch_array($resultNearby))
{
	if(file_exists($imagefolder.$rowNearby['image']))
	{
		$startlink = '<a href="'.$_SERVER['PHP_SELF'].'?id='.$rowNearby['id'].'&show=1&pageno='.$_GET['pageno'].'&pageid='.$catalogpageid.'&categoryid='.$_GET['categoryid'].'">';
		$ext = '.'.end(explode('.', $rowNearby['image']));
		$tablenameCount = strlen($tablename);
		$numberChar = $tablenameCount + 14;
		$photo = substr($rowNearby['image'], 0, $numberChar).'_thumb'.$ext;
		$return .= '<td>';
		$return.=$startlink.'<img src="'.$imagefolder.$photo.'" style="float:left; margin:10px 5px; border:3px solid #FFF;" />'.$endlink;
		$return .= '</td>';
	}
}
$return .= '</tr></table>';

```

---

<div class="post-metadata">

### Author: ![djheru](https://yyz1.discourse-cdn.com/flex011/user_avatar/forum.kirupa.com/djheru/32/3190_2.png) [@djheru](https://forum.kirupa.com/u/djheru)
#### Post date: [May 29, 2008, 2:36pm UTC](https://forum.kirupa.com/t/mysql-contiguous-records/261559/9 "2008-05-29T14:36:55Z")

</div>

```php

$more = $_GET['id'] - 2;
$less = $_GET['id'] + 2;
$whereNearby = 'WHERE id >= '.$more.' AND id <= '.$less; 

```

This won’t work as you expect. Imagine that $\_GET[‘id’] = 10. You would end up with this query conditional:  
WHERE id\>=8 AND id\<=12  
Now imagine that the record with an ID of 8 and the one with the record of 9 have been deleted from the database…

If I understand your request correctly, you want to get 5 photos. One with a given ID, and the two that are immediately before and after IF you order by datecreated.

So the first thing that you need to figure out is the value of datecreated where the ID=$id. Now you could use two queries for this, or you could use a subquery. The subquery (SELECT datecreated FROM mytable WHERE id = $id) is executed first, and it stores the value of datecreated where ID=$id.

Then, you can use this value to get the two where datecreated is less than and the two that are greater than THE datecreated we selected.

I think that it would be possible to use UNION to get the proper result as well if ID refers to the auto increment value of the table, but I wasn’t sure that it did. What if ID refers to the user ID or the Gallery ID?

---

<div class="post-metadata">

### Author: ![Charleh](https://avatars.discourse-cdn.com/v4/letter/c/a9a28c/32.png) [@Charleh](https://forum.kirupa.com/u/Charleh)
#### Post date: [May 29, 2008, 3:30pm UTC](https://forum.kirupa.com/t/mysql-contiguous-records/261559/10 "2008-05-29T15:30:30Z")

</div>

You don’t want to run multiple queries when there is no need. This can be done with one query…

Here’s what I’ve got in MSSQL

```auto

SELECT * from Pics WHERE ID IN 
(SELECT TOP 2 ID FROM Pics WHERE CreatedDate > (SELECT CreatedDate FROM Pics WHERE ID = '5') ORDER BY CreatedDate) 
OR ID IN 
(SELECT TOP 3 ID FROM Pics WHERE CreatedDate <= (SELECT CreatedDate FROM Pics WHERE ID = '5) ORDER BY CreatedDate DESC) 

```

And in MySQL (not 100% but…)

```auto

SELECT * from Pics WHERE ID IN 
(SELECT ID FROM Pics WHERE CreatedDate > (SELECT CreatedDate FROM Pics WHERE ID = '5') ORDER BY CreatedDate LIMIT 2) 
OR ID IN 
(SELECT ID FROM Pics WHERE CreatedDate <= (SELECT CreatedDate FROM Pics WHERE ID = '5) ORDER BY CreatedDate DESC LIMIT 3) 

```

Works for me on my Great Plains data I think, lemme just test a bit

Edit: Yeah works, just tweaked the \> and \<= to the correct way round, should be all you need

---

<div class="post-metadata">

### Author: ![kdd](https://avatars.discourse-cdn.com/v4/letter/k/ecb155/32.png) [@kdd](https://forum.kirupa.com/u/kdd)
#### Post date: [May 29, 2008, 4:02pm UTC](https://forum.kirupa.com/t/mysql-contiguous-records/261559/11 "2008-05-29T16:02:33Z")

</div>

Thanks for the info. 🙂

---

<div class="post-metadata">

### Author: ![imagined](https://avatars.discourse-cdn.com/v4/letter/i/a87d85/32.png) [@imagined](https://forum.kirupa.com/u/imagined)
#### Post date: [June 3, 2008, 1:29pm UTC](https://forum.kirupa.com/t/mysql-contiguous-records/261559/12 "2008-06-03T13:29:43Z")

</div>

I just ran Charleh query on phpMyAdmin, this is what I got:

Error  
SQL query:

SELECT \*  
FROM projects  
WHERE ID  
IN (

SELECT ID  
FROM projects  
WHERE CreatedDate \> (  
SELECT CreatedDate  
FROM projects  
WHERE ID = ‘5’ )  
ORDER BY CreatedDate  
LIMIT 2  
)  
OR ID  
IN (

SELECT ID  
FROM projects  
WHERE CreatedDate \<= (  
SELECT CreatedDate  
FROM projects  
WHERE ID = ‘5’ )  
ORDER BY CreatedDate DESC  
LIMIT 3  
)  
LIMIT 0 , 30

MySQL said:

#1235 - This version of MySQL doesn’t yet support ‘LIMIT & IN/ALL/ANY/SOME subquery’

Does anyone know any alternative to this?

---

<div class="post-metadata">

### Author: ![Charleh](https://avatars.discourse-cdn.com/v4/letter/c/a9a28c/32.png) [@Charleh](https://forum.kirupa.com/u/Charleh)
#### Post date: [June 3, 2008, 2:34pm UTC](https://forum.kirupa.com/t/mysql-contiguous-records/261559/13 "2008-06-03T14:34:32Z")

</div>

Yeah, use MSSQL 😃

But seriously, I just read up on it - MySQL doesn’t support LIMIT in subqueries used in the WHERE clause - however you can still create a subquery and JOIN to it and it should be ok!

Try the following

```auto

SELECT * 
FROM projects
INNER JOIN 
(
SELECT ID
FROM projects
INNER JOIN ( 
SELECT CreatedDate
FROM projects
WHERE ID = '5') as Dates ON projects.CreatedDate > Dates.CreatedDate
ORDER BY CreatedDate
LIMIT 2
) as projInfo ON projInfo.ID = projects.ID
LIMIT 0 , 30
 

```

I’m not 100% on MySQL syntax so the aliasing of the subqueries might be incorrect - but play about and hopefully you get the idea

Tested it in MSSQL

Query was as follows

```auto

[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]SELECT [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]* [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]from[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000] pics [/COLOR][/SIZE]
[SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]INNER [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]JOIN[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]SELECT [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]TOP[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000] 2 ID [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]FROM[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000] pics [/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]INNER [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]JOIN [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]SELECT[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000] CreatedDate [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]FROM[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000] pics [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]WHERE[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000] ID [/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]= [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]'5'[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]) [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]as[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000] Dates [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]ON[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000] pics[/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000]CreatedDate [/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]>[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000] Dates[/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000]CreatedDate [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]ORDER [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]BY[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000] pics[/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000]CreatedDate[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]UNION[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]SELECT [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]TOP[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000] 3 ID [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]FROM[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000] pics [/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]INNER [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]JOIN[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]SELECT[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000] CreatedDate [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]FROM[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000] pics [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]WHERE[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000] ID [/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]= [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]'5'[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]as[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000] Dates [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]ON[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000] pics[/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000]CreatedDate [/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]<=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000] Dates[/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000]CreatedDate [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]ORDER [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]BY[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000] pics[/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000]CreatedDate [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]DESC[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]as[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000] PicIDs [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]on[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000] PicIDs[/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000]ID [/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000] pics[/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000]ID[/COLOR][/SIZE]
[/COLOR][/SIZE][/COLOR][/SIZE]

```

All I did was UNION the two result sets for the date query in the subquery and JOIN to it to get the pic data. Also notice that I’ve used JOINs instead of WHEREs to get the actual IDs for the pictures based on date order

I’m sure something similar will work in MySQL

---

<div class="post-metadata">

### Author: ![imagined](https://avatars.discourse-cdn.com/v4/letter/i/a87d85/32.png) [@imagined](https://forum.kirupa.com/u/imagined)
#### Post date: [June 3, 2008, 3:24pm UTC](https://forum.kirupa.com/t/mysql-contiguous-records/261559/14 "2008-06-03T15:24:33Z")

</div>

I just ran:

```auto

SELECT * 
FROM projects
INNER JOIN 
(
SELECT ID
FROM projects
INNER JOIN ( 
SELECT datecreated
FROM projects
WHERE ID = '5') as Dates ON projects.datecreated > Dates.datecreated
ORDER BY datecreated
LIMIT 2
) as projInfo ON projInfo.ID = projects.ID
LIMIT 0 , 30

```

and gave me error

```auto
MySQL said:  

#1052 - Column 'datecreated' in order clause is ambiguous 

```

---

<div class="post-metadata">

### Author: ![Charleh](https://avatars.discourse-cdn.com/v4/letter/c/a9a28c/32.png) [@Charleh](https://forum.kirupa.com/u/Charleh)
#### Post date: [June 3, 2008, 4:24pm UTC](https://forum.kirupa.com/t/mysql-contiguous-records/261559/15 "2008-06-03T16:24:40Z")

</div>

This just means that you (or I) didn’t specify which datecreated to order by as there’s a datecreated in the projects table and a datecreated in the subquery - simply change the ORDER BY datecreated to add the table prefix (you should be ordering by the projects table) i.e.

```auto

SELECT * 
FROM projects
INNER JOIN 
(
SELECT ID
FROM projects
INNER JOIN ( 
SELECT datecreated
FROM projects
WHERE ID = '5') as Dates ON projects.datecreated > Dates.datecreated
ORDER BY projects.datecreated -- added table prefix here
LIMIT 2
) as projInfo ON projInfo.ID = projects.ID
LIMIT 0 , 30

```

---

<div class="post-metadata">

### Author: ![imagined](https://avatars.discourse-cdn.com/v4/letter/i/a87d85/32.png) [@imagined](https://forum.kirupa.com/u/imagined)
#### Post date: [June 3, 2008, 4:55pm UTC](https://forum.kirupa.com/t/mysql-contiguous-records/261559/16 "2008-06-03T16:55:17Z")

</div>

Thanks Charleh, the query was successful. I ran this:

```auto
SELECT * 
FROM projects
INNER JOIN 
(
SELECT ID
FROM projects
INNER JOIN ( 
SELECT datecreated
FROM projects
WHERE ID = '229') as Dates ON projects.datecreated > Dates.datecreated
ORDER BY projects.datecreated -- added table prefix here
LIMIT 2
) as projInfo ON projInfo.ID = projects.ID
LIMIT 0 , 30

```

and gave me two rows with ids 274 and 278 but shouldn’t have given me 228 and 230?

---

<div class="post-metadata">

### Author: ![djheru](https://yyz1.discourse-cdn.com/flex011/user_avatar/forum.kirupa.com/djheru/32/3190_2.png) [@djheru](https://forum.kirupa.com/u/djheru)
#### Post date: [June 3, 2008, 5:52pm UTC](https://forum.kirupa.com/t/mysql-contiguous-records/261559/17 "2008-06-03T17:52:50Z")

</div>

Did you try my query? I am just curious to see if it works.

---

<div class="post-metadata">

### Author: ![imagined](https://avatars.discourse-cdn.com/v4/letter/i/a87d85/32.png) [@imagined](https://forum.kirupa.com/u/imagined)
#### Post date: [June 3, 2008, 6:41pm UTC](https://forum.kirupa.com/t/mysql-contiguous-records/261559/18 "2008-06-03T18:41:16Z")

</div>

[QUOTE=djheru;2335752]Did you try my query? I am just curious to see if it works.[/QUOTE]

I ran this

```auto
SELECT id, name, datecreated FROM projects
WHERE
     (datecreated >=
          (SELECT datecreated FROM projects WHERE id = 229)
     LIMIT 3)
OR
     (datecreated <
          (SELECT datecreated FROM projects WHERE id = 229)
     LIMIT 2)

```

and got this

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘LIMIT 3 ) OR ( datecreated \< ( SELECT datecreated FROM projects WHERE id = 22’ at line 1

---

<div class="post-metadata">

### Author: ![Charleh](https://avatars.discourse-cdn.com/v4/letter/c/a9a28c/32.png) [@Charleh](https://forum.kirupa.com/u/Charleh)
#### Post date: [June 4, 2008, 8:06am UTC](https://forum.kirupa.com/t/mysql-contiguous-records/261559/19 "2008-06-04T08:06:07Z")

</div>

[quote=imagined;2335690]Thanks Charleh, the query was successful. I ran this:

```auto
SELECT * 
FROM projects
INNER JOIN 
(
SELECT ID
FROM projects
INNER JOIN ( 
SELECT datecreated
FROM projects
WHERE ID = '229') as Dates ON projects.datecreated > Dates.datecreated
ORDER BY projects.datecreated -- added table prefix here
LIMIT 2
) as projInfo ON projInfo.ID = projects.ID
LIMIT 0 , 30

```

and gave me two rows with ids 274 and 278 but shouldn’t have given me 228 and 230?[/quote]

No - 2 reasons

1. because it’s ordered by date - if you do SELECT \* FROM projects ORDER BY datecreated and look for ID 229 in the list you should find 274 and 278 right after it. If you want it in ID order just change the

```auto
ORDER BY projects.datecreated 

```

to

```auto
ORDER BY projects.ID

```

1. because this query simply gets the two rows AFTER the specified row based on the given row ID and order supplied

All you need to do now is get the original row and the two before it; if you look at the original question it’s two rows after and two rows before - 4 extra rows in total. You can do it by unioning the extra data within the subquery - I’m sure that will work. I’ve done it in MSSQL above - can you work out how to do it for MySQL yourself or do you need a hand?