473,322 Members | 1,409 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

max() over some rows but grouped, within one view def - how ?

Hi,

I would appreciate any help on the following problem:

Suppose I have a table (simplified) (vacc = vaccination)

vacc_def
fk_indication
seq_no
age_due_min
age_due_max
...

where seq_no gives the sequence number of a particular
vaccination event definition.

Now, if I want to find the last scheduled vaccination for the
known indications I can run this query:

select fk_indication, max(seq_no) from vacc_def group by fk_indication;

This works as expected.

I now want to create a view with a column is_last_shot
that is TRUE where
seq_no = (
select max(seq_no)
from vacc_def
where fk_indication = <some indication PK>
)

Obviously, one would use a CASE construct to set the (virtual)
column is_last_shot to either true or false depending on the
value of seq_no compared to max(seq_no) for that
indication. However, how do I know <some indication PK> in
the view definition ?!?

Another possibility would be to use UNION to aggregate the
queries per fk_indication but that means one needs to know the
fk_indication values at view creation time which isn't
technically sound.

Or do I have to resort to writing a plpgsql function employing
a LOOP construct ?

I can post the full table/view defs and data if needed or you
can find them here:

http://savannah.gnu.org/cgi-bin/view...ed/server/sql/

-> gmclinical.sql (tables)
-> gmClinicalViews.sql (views)
-> gmClinicalData.sql (data)
-> country.specific/de/STIKO-Impfkalender.sql (more data)

Thanks,

Karsten Hilbert, MD
(www.gnumed.org)

PS: Yes, I did order "SQL for Smarties" courtesy of this
list's suggestion ;-)
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #1
0 1187

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: JaNE | last post by:
Maybe this isn't the most apropriate place to ask mysql related question, but I think it is close enought to most php users and I always got find answers on this group. well, I need to count how...
2
by: Eric | last post by:
please help to select these rows from these tables my tables are table1 table1Id groupId table2id price 1 1 1 10 2 1 3 1000 3 1 ...
12
by: Rick DeBay | last post by:
I'm trying to create a layout table, where the spacing between rows varies. I've tried using setting margin-top and border-top for the rows I wan't spaced down from the one above, and I've also...
3
by: csk | last post by:
Hopefully someone will have some ideas on how to do this. I'm officially stumped. I have two entities to join. Simplified descriptions follow: The first has names and addresses...
5
by: David Wender | last post by:
I want to create a dataview with a sort on multiple columns. However, when I use FindRows, I only want to search some of the columns, not all. Is this possible? I have not been able to make it...
2
by: brob | last post by:
I am Access newbie looking for some guidance in putting together a query that can output a table of max values in a given column grouped by a couple of other columns. Here is an example of what I...
8
by: nico3334 | last post by:
I have a database that has a Date column (1/1/2007) and a Data column that has numerical data. I am currently running a query to sum the Data column for a certain month and grouped by day (There is...
9
by: Summercool | last post by:
I wonder to show the images in a table format... and to set a box of width 100px and height 100px, is there a way? Right now, if I set width to 100px, then height can be 180px... If I set...
9
by: sitko | last post by:
Hi, I have an Order tracking spreadsheet that I need help with. I have a 2 worksheets "Open", and "Closed". I have entries on the "Open" sheet which may or may not be grouped together. I've...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.