Re: Insert First Row

["Tom Jedrzejewicz" <tomjedrz@xxxxxxxxx>]

This is selecting records where XMMAIL and XMSmn1 are "distinct".  You want
to use GROUP BY on the column (or columns) that you want to have only oneof,
and a summarization command (min, max, average, sum) on the remaining
columns.

Something like ...
INSERT INTO NEWSYS/EMLCMP (EMLADDR, EMEMP#) SELECT XMMAIL,
min(XMSmn1) FROM posmstp WHERE xmmail <> ' '  GROUP BY XMMAIL

Have fun ...

On 10/3/06, Michael Ryan <michaelrtr@xxxxxxxxx> wrote:
> I thought I could do this, but it's not working for me. Here's my SQL
> statement:
>
> INSERT INTO NEWSYS/EMLCMP (EMLADDR, EMEMP#) SELECT DISTINCT XMMAIL,
> XMSmn1 FROM posmstp WHERE xmmail <> ' '
>
> I have records in posmstp like this:
>
> XMMAIL     XMSMN1
> A_Value      501
> B_Value      123
> A_Value      789
> C_Value      555
>
> I want the resultant table to be:
>
> XMMAIL     XMSMN1
> A_Value      501
> B_Value      123
> C_Value      555
>
> In other words, I just want the values from the first XMMAIL record I
> encounter. I though the DISTINCT would do that. What do I need to do?
>
> TIA
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.