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

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

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" <REMOVE_BEFORE_REPLYING_dportas@acm.org> wrote in message
news:l8OdnRTLyKaGrUqiRVn-hA@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
> --
>
>