> > There are 5 vaccinations in a given vaccination schedule.
> >
> > Patient had 3 shots.
> >
> > I want the view to show me that shot 4 and 5 are missing
> > without having to enter the cardinality of the vaccination in
> > the original data.
>
> That sounds like you are trying to abuse the data model, so I'm not
> surprised that it isn't easily possible. As the data stored in a table
> is inherently unordered,
I know. I don't expect rows in tables to be ordered in any
way.
> you can't really talk about order unless you
> impose it yourself by way of assigning ordinal numbers or some other
> sort key to your rows.
Here is the bit of data that I forgot to mention: Those
consecutive immunization rows *are* ordered by a sort key that
the application assigns -- the date of application. So,
basically, what I want to do is the following (high level):
1) read given vaccinations from table
2) order by date_given
3) assign ordinals to the rows in the order obtained by 2)
4) deduce missing shots by joining to another table that
defines the number of vaccinations in a schedule
Sure, I can do this in client code just fine. I would prefer
to put that into a view, however. Having "output row numbers"
doesn't make that *easy* but it seems it's the missing link to
making it *possible* in SQL. I am not asking for "row numbers"
for tables - which is nonsense - but rather for optional
numbering of query result rows.
> Even if you could, say, assign a fixed order to tables or views or
> actually had some kind of automatic row number available, that would
> still make the semantics of your data dependent of the particularities
> of the queries that you use to access it, which doesn't sound like a
> good idea to me.
Understood. That wasn't what I was after. I poorly presented
the case.
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
