Article: Selecting Based on a Version Field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Site Related Forums

Article Discussion

Article: Selecting Based on a Version Field

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-06-10 : 12:15:11

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.

Article Link.

rythm123us
Starting Member

27 Posts

Posted - 2006-03-15 : 10:43:00

I have looked at the post and have a simple question. Is it better to use the "WHERE" clause or use an "INNER JOIN"?

Thanks,
Rythm

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-03-15 : 11:52:59

use inner join to join table.
use where to filter data from the joined tables.

Go with the flow & have fun! Else fight the flow

jhermiz

3564 Posts

Posted - 2006-03-16 : 18:32:30

I think he / she met performance wise would it be better to do a select with an inner select and use a WHERE statement to join the two tables or whether joining the tables is better. I would say that using the join would be more readable and would be a better performing query.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

Player_One
Starting Member

5 Posts

Posted - 2007-01-19 : 09:43:44

I have a solution that returns all rows of the original table. Makes it more flexible if you have additional columns that may be added or removed. I did this in a view to return just the newest revisions.

To apply this to Sherry's example, change my version to her lta_id and my build to her version.

SELECT *
FROM Revisions as AllRevs
WHERE EXISTS
   (SELECT NewRevs.version
    FROM Revisions as NewRevs
    WHERE AllRevs.version = NewRevs.version
    GROUP BY NewRevs.version
    HAVING MAX(NewRevs.build)=AllRevs.build)
ORDER BY AllRevs.version

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources