Returning Max values for groups of records.

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

SQL Server 2000 Forums

SQL Server Development (2000)

Returning Max values for groups of records.

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-21 : 09:13:12

Rod writes "I have a table that stores revisions of records. Each record in the history table has a a revision number and a id. Every time a revision is made to the record for a specific id the revision number increments. For example:

ID: 1 REV_NUM: 0
ID: 2 REV_NUM: 0
ID: 2 REV_NUM: 1
ID: 3 REV_NUM: 0
ID: 3 REV_NUM: 1
ID: 3 REV_NUM: 2

ID and REV_NUM are the column names. The problem that I am having is that I need to get a list of the most recent revisions based on the revision number. So from the list above I should get:

ID: 1 REV_NUM: 0
ID: 2 REV_NUM: 1
ID: 3 REV_NUM: 2

How would I be able to write the SQL to retrieve the most current revision for all the ids. I can get the value for a single id but I need a list of all of them.

Select ID, MAX(REV_NUM) from FOO where ID = 3

This will return:

ID: 3 REV_NUM: 2

This is good for one but I need the whole list.

Thanks for your help

Rod"

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-03-21 : 09:54:59

You would use GROUP BY.


select ID, MAX(REV_NUM) 
from FOO
group by ID

For each group ( in this case rows with the same ID, since you've indicated GROUP BY ID ) in the rowset you've specified in the FROM and WHERE clause, compute the maximum REV_NUM.

setBasedIsTheTruepath
<O>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources