Group By Function help - 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 2008 Forums

Transact-SQL (2008)

Group By Function help

Author

Topic

DunkinD
Starting Member

2 Posts

Posted - 2011-02-01 : 09:46:09

I have 3 rows for a certain claim. 2 of the rows contain the same ClaimNBR (123) and prodnum (456) but those rows have a sequence of 1 and 2. The third row contains a different claimnbr (987) and prodnum (135). I need to return the lowest row (1) for claimnbr 123 and then claimbr 987. So my finaly result will have 2 rows.

I am able to do that but there are other fields I need to return as well. The CLAIMPRODNUM and CLAIM_IND fields are different values in claimnbr (123). So when I include them in the select statement it still pulling back 3 rows. Here is the query I am using below. Any help would be great.

select a.CLAIMPRODNUM ,a.claimnbr , a.prodnum, b.min_seq ,
a.claimseq, a.CLAIM_IND
from claim a
inner join (select claimnbr,prodnum, min(prodseq) as min_seq
from claim
group by claimnbr, prodnum) b
on a. claimnbr = b. claimnbr AND
a. prodnum = b. prodnum

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2011-02-01 : 10:46:42

Didn't get your question clearly but here is my shot. Also try explain your issue in table. It's easier to understand

Select a.CLAIMPRODNUM ,a.claimnbr , a.prodnum, b.min_seq ,
a.claimseq, a.CLAIM_IND
from claim a
inner join (select claimnbr,prodnum, min(prodseq) as min_seq
from claim
group by claimnbr, prodnum) b
on a. claimnbr = b. claimnbr AND
a. prodnum = b. prodnum
and a.prodseq = b.min_seq

DunkinD
Starting Member

2 Posts

Posted - 2011-02-01 : 10:54:55

Sorry for not explaining correctly. But yes your response helped. I got it working. Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources