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.

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.


Related Articles

Joining to the Next Sequential Row (2 April 2008)

Writing Outer Joins in T-SQL (11 February 2008)

How to Use GROUP BY with Distinct Aggregates and Derived tables (31 July 2007)

How to Use GROUP BY in SQL Server (30 July 2007)

SQL Server 2005: Using OVER() with Aggregate Functions (21 May 2007)

Server Side Paging using SQL Server 2005 (4 January 2007)

Using XQuery, New Large DataTypes, and More (9 May 2006)

Counting Parents and Children with Count Distinct (10 January 2006)

Other Recent Forum Posts

Filter query base on date field (3d)

Pull results from a stored procedure (3d)

Help With Query To Count Number of Pets & Vaccinations (4d)

Connect sql off network (4d)

Moving Registered Servers to a New Instance (8d)

CU 31 for 2017 Will Not Install (8d)

I get different results when I scan from a barcode reader with a different language (10d)

Spatial query in SQL server (12d)

- Advertisement -