Hi everyone! I'm blaming this prob 1

[Rob999]

Hi everyone! I'm blaming this problem on Monday fog, but I can't seem to return the record that has a max value in it. It's easy to return:

SELECT Account, Max(Points)
FROM Table
GROUP BY Account

but when I want to include other fields from that particular record with the resultset I'm being forced to use another aggregation which I don't want to do. I want the other fields from this specific maximum point record! If I try:

SELECT Account, Max(Points), Type
FROM Table
GROUP BY Account

I get the expected error message that Type must be included in the aggregation or group by lines - I don't want any aggregation on Type, I want the Type that goes with the record that has the maximum points for that account.

When I put it in the GROUP BY as below:

SELECT Account, Max(Points), Type
FROM Table
GROUP BY Account, Type

to eliminate the error message, it gives incorrect records in many cases, with the Max(Points) not given, just Max(Points) where the Type value is greatest.

I'd sure appreciate any help. Thanks!

 

[sunila7]

Hi,

Try this query...

Select * from TBL B
Inner Join
(SELECT Account, Max(Points) maxpoints
FROM TBL
GROUP BY Account) TBL1 ON TBL.Account=TBL1.Account and Tbl.maxpoints=B.points

Sunil

 

[Rob999]

Thanks for the quick response Sunil. I'm having a bit of trouble with the implementation, so I'll show you the field names I'm working with (Don't show anyone else, it's secret!) Here's how I attempted to implement your suggestion:

Select * from T_SubsetCFPDR_2 B
Inner Join
(SELECT ACCTDR, Max(PNTSDR) as maxpoints
FROM T_SubsetCFPDR_2
GROUP BY ACCTDR) T_SubsetCFPDR_2 ON T_SubsetCFPDR_2.ACCTDR=T_SubsetCFPDR_2.ACCTDR
and T_SubsetCFPDR_2.maxpoints=B.PNTSDR

Clearly, I'm missing something. If I included the 1's to differentiate the tables in the join sections, I got errors. The query above ran on and produced a Cartesian effect - 400,000+ rows and growing, in a resultset that has about 500 distinct accounts. As you can see, T_SubsetCFPDR_2 is the cleverly named table. Thanks again.

 

[sunila7]

Hi,

Try this....

Select * from T_SubsetCFPDR_2 B
Inner Join
(SELECT ACCTDR, Max(PNTSDR) as maxpoints
FROM T_SubsetCFPDR_2
GROUP BY ACCTDR) TBL ON TBL.ACCTDR=B.ACCTDR
and TBL.maxpoints=B.PNTSDR


Sunil

 

[swampBoogie]

Select * from T_SubsetCFPDR_2 B
Inner Join
(SELECT ACCTDR, Max(PNTSDR) as maxpoints
FROM T_SubsetCFPDR_2
GROUP BY ACCTDR) T_SubsetCFPDR_2
ON T_SubsetCFPDR_2.ACCTDR = B.ACCTDR
and T_SubsetCFPDR_2.maxpoints = B.PNTSDR

 

[Rob999]

Thanks guys!

Sunila's post worked like a charm (I used it before yours showed up SB), so please accept a star as I found it extremely helpful! Thanks again.