Alexey Pechnikov <[email protected]> wrote:
> I did get this frustrate behaviour in my prototype of the versioning
> datastore where all versions of records are stored permanently. In my
> example the foreign identifiers are stored in the user table and all rows
> versions are stored in the user_record table. For visualization we need to
> get only last versions of records
But view_user statement makes no attempt to select the last version. It picks
some arbitrary random version. You might want to consider something like this:
CREATE VIEW view_user AS
SELECT * FROM user_record
WHERE record_id in
(select max(record_id) from user_record group by user_id)
ORDER BY name ASC;
-- or
CREATE VIEW view_user AS
SELECT * FROM user_record r1
WHERE r1.record_id =
(select max(record_id) from user_record r2 where r1.user_id = r2.user_id)
ORDER BY name ASC;
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
