RE: Insert First Row

["Richard Casey" <casey_r@xxxxxxxxxxxxxxxx>]

Michael,

DISTINCT looks at all the fields, not just XMMAIL. If you had records in
posmstp with the same XMMAIL and XMSmn1 values, those would generate one
record.

Try this

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

That will pick the smallest value of XMSmn1 to insert, not necessarily the
first encountered.

Hope this helps!

Richard



-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Michael Ryan
Sent: Tuesday, October 03, 2006 2:07 PM
To: Midrange Systems Technical Discussion
Subject: Insert First Row


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

--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.407 / Virus Database: 268.12.12/461 - Release Date: 10/2/2006