I have a database where everything is versioned inside itself using `start_date` and `end_date`

What I want to do is dynamically get a version number for a row.

So, get all rows with a particular item\_id ordered by start\_date and then given a `start_date` and `end_date` that will isolate it down to one row. what I want is to find out where that row sits.

e.g. the data

```
id | item_id | start_date
1  | 1       | 01-31-2012
2  | 1       | 02-31-2012
3  | 2       | 02-31-2012
4  | 1       | 03-31-2012
5  | 2       | 05-31-2012
6  | 1       | 04-31-2012

```

if using `select id from items where item_id = 2 and start_date = 05-31-2012`

would return `5` so what I would want for the version would be `2`

if using `select id from items where item_id = 1 and start_date = 03-31-2012`

would return `4` so what I would want for the version would be `3`

The only way I can think of to do this would be to select all rows with the item\_id of whatever, and then loop over each item checking the start\_date, if it is not what I want increase version\_number by 1.

Is there a way to do it with just mysql?