Top Sub Query Selection

I need help selecting only the top one of a sub query.

Simplified Example Included.

What I need is a query to only select min apr of the group amortizationtype,pointgroup,term.

What I get now...
1031      40900      123456      654432      3.197      fixed      1      180
1031      40901      123456      654432      3.322      fixed      1      180
1031      40886      123456      654432      3.957      fixed      1      360
1031      40887      123456      654432      4.082      fixed      1      360
1031      40902      123456      654432      3.072      fixed      2      180
1031      40885      123456      654432      3.831      fixed      2      360

What I want is..

1031      40900      123456      654432      3.197      fixed      1      180
1031      40886      123456      654432      3.957      fixed      1      360
1031      40902      123456      654432      3.072      fixed      2      180
1031      40885      123456      654432      3.831      fixed      2      360


DECLARE @LoanRequestId INT = 123456, @LoanRequestCreatedID INT = 654432
DECLARE @TempTable TABLE(
		RecordID INT IDENTITY(1,1),
		userid INT,
		quoteid INT,
		apr DECIMAL(8,3),
		pointgroup INT,
		term INT,
		amortizationtype VARCHAR(25))
		
INSERT @TempTable (userid,quoteid,apr,pointgroup,term,amortizationtype) VALUES(1031,40900,3.197,1,180,'fixed')
INSERT @TempTable (userid,quoteid,apr,pointgroup,term,amortizationtype) VALUES(1031,40901,3.322,1,180,'fixed') 
INSERT @TempTable (userid,quoteid,apr,pointgroup,term,amortizationtype) VALUES(1031,40902,3.072,2,180,'fixed') 
INSERT @TempTable (userid,quoteid,apr,pointgroup,term,amortizationtype) VALUES(1031,40885,3.831,2,360,'fixed') 
INSERT @TempTable (userid,quoteid,apr,pointgroup,term,amortizationtype) VALUES(1031,40886,3.957,1,360,'fixed') 
INSERT @TempTable (userid,quoteid,apr,pointgroup,term,amortizationtype) VALUES(1031,40887,4.082,1,360,'fixed')
INSERT @TempTable (userid,quoteid,apr,pointgroup,term,amortizationtype) VALUES(1031,40889,4.000,4,360,'fixed')

SELECT  userid,quoteid,@LoanRequestId AS LoanRequestID,@LoanRequestCreatedID AS LoanRequestCreatedID,apr,amortizationtype,pointgroup,term
FROM @TempTable
WHERE pointgroup <= 3		
GROUP BY userid, amortizationtype,pointgroup,term,quoteid,apr

Open in new window

CodeMarxAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dqmqCommented:
Test script was so helpful.  Needed to correct the last insert, though.  Also, I presume you also want to group by userid; if not just remove the 3 lines where it is mentioned. :>)


select * from @temptable t1
inner join
(
select amortizationtype,pointgroup,term, min(apr) minapr
,userid
from @temptable
group byamortizationtype,pointgroup,term
,userid
) t2
  on   t1.amortizationtype=t2.amortizationtype
  and t1.pointgroup=t2.pointgroup
  and t1.term=t2.term
  and t1.apr = t2.minapr
  and t1.userid = t2.userid
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CodeMarxAuthor Commented:
Perfect... Thanks for your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.