Need some help with a query - SQL Server Forums

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)

Need some help with a query

Author

Topic

Munchausen
Starting Member

25 Posts

Posted - 2005-07-26 : 23:30:11

I'm kind of stumped on the best way to do a query.

Here's the situation. Each row has a bondnumber, claimnumber, and date field, among others. A bond number is a broad category, with many claim numbers associated with it. Each time there's a change to a claim, an entirely new record is written with a new date. So there will be multiple records for each claim, allowing us to see how the claim has progressed over time.

I need to get the most recent results for several claims, based on bonds.

I used a query like this:


select bondnumber, claimnumber, max(date) 'Date'
from table
where bondnumber in ('12345','12346','12347')
group by bondnumber, claimnumber

This seems to work for showing me the most recent record for each claim associated with the bonds I specify.

The problem is this: for these records the query returns, I want to show other columns besides these three. If I just add them to the select and the group by clause, I start returning too many rows. And I can't use max, as it will not always be the correct value.

Any ideas?

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-07-27 : 00:07:26

try joining the result from your query with table or use subquery

--------------------
keeping it simple...

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-07-27 : 00:11:46

select table.bondnumber, table.claimnumber, table.date, table.col1, table.col2, table.col3 ... etc.
from table join
(select bondnumber, claimnumber, max(date) 'Date'
from table
where bondnumber in ('12345','12346','12347')
group by bondnumber, claimnumber) as A
on table.bondnumber = A.bondnumber and table.claimnumber = A.claimnumber and table.date = A.date

Munchausen
Starting Member

25 Posts

Posted - 2005-07-27 : 14:48:15

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources