Re: Insert First Row -- MIDRANGE-L

That made me think Rob...I went back to the user and asked the question
again. We worked through it and came up with a better solution. Thanks for
the job. I still will use the MIN function, but now on a different value.
Makes better sense.

On 10/3/06, rob@xxxxxxxxx <rob@xxxxxxxxx> wrote:


Why would one "not care"?  If they are not DISTINCT then how do you know
which is the right address?  Or, is it not important to get it right, just
stuff any old employee number in there?  If so, they why not use
> INSERT INTO NEWSYS/EMLCMP (EMLADDR, EMEMP#)
> SELECT XMMAIL, 0
> FROM posmstp
> WHERE xmmail <> ' '
> GROUP BY XMMAIL


Rob Berendt
--
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





"Michael Ryan" <michaelrtr@xxxxxxxxx>
Sent by: midrange-l-bounces@xxxxxxxxxxxx
10/03/2006 02:27 PM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
"Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
cc

Subject
Re: Insert First Row






Thanks Richard...that worked! I just need one value - I don't care which
one. The smallest is fine. It didn't work when I did a group by both
fields
- is that because it had to bring all the values, even with the sum on the
select?

On 10/3/06, Richard Casey <casey_r@xxxxxxxxxxxxxxxx> wrote:
>
> 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
>
>
> --
> 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.
>
>
--
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.


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