Getting mysql row version number?

Question

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?

How do you define the version as 2 and 3 in the above example. Please explain in detail. — Nilam Doctor, Nov 20 '12 at 23:56
@NilamDoctor because the matching rows with the selected dates are the 2nd and 3rd rows with that item_id. — Hailwood, Nov 21 '12 at 02:08

score 2 · Accepted Answer · answered Nov 20 '12 at 23:54

2

SELECT COUNT(*) version
FROM items
WHERE item_id = 2 AND start_date <= '2012-05-31'

or id <= 5 instead of date comparison (assuming id is unique and grows monotonously)

answered Nov 20 '12 at 23:54

zerkms

249,484
69
436
539

this wouldn't work for the add new version such as SELECT COUNT(*) version FROM test7 WHERE item_id = 1 AND start_date <= '2012-05-30'; – Drew Nov 21 '12 at 00:17
@Drew Pierce: I'm not sure I understand what you mean. The query does exactly what OP explained in the question. – zerkms Nov 21 '12 at 00:21
1

except when he said , if it is not what I want increase version_number by 1. – Drew Nov 21 '12 at 00:21
@Drew Pierce: I'm sure it's alright. If you think it's wrong - ask the OP on the question comments to clarify this case for you. – zerkms Nov 21 '12 at 00:23

GKV · Answer 2 · 2012-11-21T01:34:35.617

0

this one works in oracle..use analytic function row_number() in inner select query as below

select version from (select row_number() over ( partition by item_id  order by start_date ) version,item.* from item) where start_date ='05-31-2012'

edited Nov 21 '12 at 01:34

answered Nov 21 '12 at 01:10

GKV

501
4
8

Getting mysql row version number?

2 Answers2