List:General Discussion« Previous MessageNext Message »
From:Michael Stroh Date:July 26 2010 9:49pm
Subject:Yet another query question
View as plain text  
Hi everyone and thanks in advance for the help. I have a query that I'd like to perform
using two tables but am not sure what the best way to perform it short of creating a loop
in my code and performing multiple queries.

I have two tables. The first table acts as a master table of sorts and Num in Table1 maps
directly to Num in Table2. One way to think of this is that I'm performing a query on
Table2 and grouping the records by MAX(version) but I am not interested in records if
state = new in Table1 for the value of Num in Table2. I've tried to give an example
below.

Table1:
Num, state
1	   final
2         new
3         final

Table2:
Num, ID,    IDt, version
1        1       100           1
1        2       101           1
1        3       102           1
2        4       100           2
2        5       103           1
3        6       100           2
3        7       103           1
3        8       104           1

Preferred result:
IDt,   ID,  Num, MAX(version)
100   6          3               2
101   2          1               1
102   3          1               1
103   7          3               1
104   8          3               1

Cheers,
Michael

Thread
Yet another query questionMichael Stroh26 Jul
  • RE: Yet another query questionGavin Towey26 Jul
  • Re: Yet another query questionGeert-Jan Brits27 Jul
    • Re: Yet another query questionMichael Stroh27 Jul