Selecting Based on a Version Field

By Bill Graziano on 10 June 2001 | Tags: SELECT

Sherry writes "I have a table with form_id, lta_id, type, version and other stuff. My users are allowed to fill out a form multiple times. I need to keep the old versions, but for computations, I only want to add up the newest version. The rest of Sherry's question is in the body of the article.

form_id   lta_id   type   version
1         10       S      1
2         29       M      1
3         10       S      2
4         12       M      1
5         29       M      2
6         10       S      3

form_id   lta_id   type   version
4         12       M      1
5         29       M      2
6         10       S      3

How do I get it? - Thanks in advance for your help. Let's start with the easy part first. You can easily get the right three columns using a GROUP BY query like this:

SELECT lta_id, type, version = MAX(version)
FROM Forms
GROUP BY lta_id, type

lta_id      type version     
----------- ---- ----------- 
12          M    1
29          M    2
10          S    3

(3 row(s) affected)

Now we need to go back and get the form_id. That's going to be a little trickier. I'm going to assume that form_id uniquely identifies a row and that lta_id, type and version also uniquely identify a row. We need to use these three fields to go back and get the form_id. We can't just group by form_id since that would return every record once.

One way to do this is to use a temporary table. We can put the results of our GROUP BY query in a temporary table and join them back to the original table. An even easier way is to use a derived table. Books Online says that a FROM clause can contain one or more derived tables, which are SELECT statements in the FROM clause referred to by an alias or a user-specified name. The result set of the SELECT in the FROM clause forms a table used by the outer SELECT statement.

Our query looks like this:

SELECT form_id,
derived.lta_id,
derived.type,
derived.version
FROM Forms,
  (SELECT	lta_id, type, version = MAX(version)
   FROM	Forms
   GROUP BY lta_id, type) as Derived
WHERE Forms.lta_id = Derived.lta_id
AND Forms.type = Derived.type
AND Forms.Version = Derived.Version
ORDER BY form_id ASC

form_id     lta_id      type version     
----------- ----------- ---- ----------- 
4           12          M    1
5           29          M    2
6           10          S    3

(3 row(s) affected)

and gives us the result we wanted. Notice that the SELECT statment is aliased to Derived. We can use the fields in derived just like we use the fields in any other table. In the SELECT statement we prefix the field names with the proper table since they exist in both Derived and Forms. We have to join the tables based on all three fields in the derived table.

Derived tables are typically faster than temporary tables. Since they can only be used inside a single SQL statement they can be more limiting though.

Sorry you had to wait so long for your answer Sherry but I hope it helps.