You need to have a unique key to do it. Otherwise it's also possible but too
complicated.

Suppose you have a PK called RowID,

Select companyID, Month, data
from sometable T1
Where RowID in 
	(Select  Top 1 RowID 
		From sometable T2 
		Where T1.companyID=T2.companyID 
		Order By Month DESC)
Order by companyID


Gary

-----Original Message-----
From: Paul Clark [mailto:pclark@a...]
Sent: Saturday, September 21, 2002 5:01 PM
To: sql language
Subject: [sql_language] Selecting the last record in a table for each
unique ID


I have a table in the following simplified format:

CompanyID  Month  Data
1           1      unique data for Month 1
1           2      data for M2
1           3      data for M3
2           1      data for M2
2           2      data for M2
3           1      data for M1

A record may or may not be added at the conclusion of each month for each 
company ID (based on sales).

I wish to retrieve the last record (ie the month with the largest number)
for *each* Company ID along with the relevant data. I'm looking for a 
query output something like:

CompanyID  Month   Data
1           3      data for M3
2           2      data for M2
3           1      data for M1  

I've tried a couple of queries using max(Month) but end up either 
excluding the records for companyID 2 and 3 or showing all the records for 
Company 1 depending on whether the data field was part of the query or not.

Select companyID, max(Month), data
from sometable 
group by companyID, data 

I think I'm missing something obvious but it has me stumped at the moment. 
Any help appreciated. Thanks.


This electronic message transmission, including any attachments, contains information from PacifiCare Health Systems Inc. which may
be confidential or privileged. The information is intended to be for the use of the individual or entity named above. If you are not
the intended recipient, be aware that any disclosure, copying, distribution or use of the contents of this information is
prohibited.  

If you have received this electronic transmission in error, please notify the sender immediately by a "reply to sender only" message
and destroy all electronic and hard copies of the communication, including attachments.