Selecting Based on a Version Field
By Bill Graziano
on 10 June 2001
| 6 Comments
| 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.
The rest of her question follows:
My data might look like this:
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
You see lta_id = 10 filled out the form 3 times. lta_id = 29 filled out the form 2 times. All I want from my result set is
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.
