QUERY Question

[Tim Morrison]

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

[David Portas]

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
--

[Tim Morrison]

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
--