469,106 Members | 2,224 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,106 developers. It's quick & easy.

QUERY Question

I have a table that contains the following:

LOANID,PAYMENTDATE,PAIDTAG,PAYMENTAMOUNT
-----------------------------------------------------------------------------------
1 01/01/04 0 100
1 02/01/04 0 100
2 01/01/04 0 100
2 02/01/04 0 100

I need to create a VIEW that contains only 1 record for each loan id where paidtag = 0

I tried a SELECT DISTINCT query but I couldnt get it working.

SELECT DISTINCT LOANID,PAYMENTDATE,PAYMENTAMOUNT
FROM MyTable
WHERE PAIDTAG = 0

does not give me distinct records for each loan id.

Any help is appreciated.


--
Tim Morrison

--------------------------------------------------------------------------------

Vehicle Web Studio - The easiest way to create and maintain your vehicle related website.
http://www.vehiclewebstudio.com
Jul 20 '05 #1
2 1038
Is this it?

SELECT loanid, MAX(paymentdate) AS paymentdate, paymentamount
FROM MyTable
WHERE paidtag = 0
GROUP BY loanid, paymentamount

If not, please post DDL for the table and show your required result.

--
David Portas
------------
Please reply only to the newsgroup
--
Jul 20 '05 #2
Thank you. That pointed me in the right direction, and it works.

My actual query ended up being:

SELECT TOP 100 PERCENT LOANID, MIN(PAYMENTNUMBER) AS PAYMENTNUMBER,
MAX(PAYMENT) AS PAYMENT, MIN(PAYMENTDATE)
AS PAYMENTDATE
FROM dbo.AMORTSchedule
WHERE (PAIDTAG = 0)
GROUP BY LOANID
ORDER BY PAYMENTNUMBER

Thanks,

Tim

"David Portas" <RE****************************@acm.org> wrote in message
news:l8********************@giganews.com...
Is this it?

SELECT loanid, MAX(paymentdate) AS paymentdate, paymentamount
FROM MyTable
WHERE paidtag = 0
GROUP BY loanid, paymentamount

If not, please post DDL for the table and show your required result.

--
David Portas
------------
Please reply only to the newsgroup
--

Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.