471,893 Members | 1,476 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,893 software developers and data experts.

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 1071
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 YellowAndGreen | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.