> > 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 ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
