SQL using Max, Group By, or possible temp table? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

SQL using Max, Group By, or possible temp table?

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-01-13 : 08:46:54

David writes "OS: Windows XP
SQl Server Version:Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15, Copyright (c) 1988-2003 Microsoft Corporation, Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

I've got a table with the following fields:
id, last_changed_date, fname, lname, code1, code2, city, state, data_status. All are stings except for the date field

Here are some examples of data values:
id,last_changed_date,fname,lname,code1,code2,city,state,data_status
18, 1/8/2006, Sandra, Biller, CE, RE, Ney York, NY, A
18, 1/9/2006, Sandra, Biller, CE, RE, Ladysmith, WI, A
18, 1/10/2006, Sandra, Biller, CE, RE, Ladysmith, WI, I
19, 1/9/2006, Steve, Smith, CE, RE, Little Rock, AR, A
19, 1/10/2006, Steve, Smith, CE, RE, La Fayette, NY, A
20, 1/10/2006, Fred, Johnson, CE, RE, La Fayette, NY, A

What I'm after is a query that will pull only the last changed record (as determined by last_changed_date), and only if it has a status of 'A' ('A' for active, 'I' for inactive). So the result of the needed query on the data set above would be:
19, 1/10/2006, Steve, Smith, CE, RE, La Fayette, NY, A
20, 1/10/2006, Fred, Johnson, CE, RE, La Fayette, NY, A

I'm guessing I may need to create a temp table and utilize the max function with a group by, but..?

"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-13 : 08:59:32

[code]select t.*
from table1 t
inner join
(
select id, max(last_changed_date) as last_change_dt
from table1
group by id
) as m
on t.id = m.id
and t.last_changed_date = m.last_change_dt
where t.data_status = 'A'[/code]

-----------------
'KH'
if you can't beat them, have someone else to beat them

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources