Re: SQL Delete question -- MIDRANGE-L


Jim - have a look at the following

http://www.systeminetwork.com/resources/artarchive/index.cfm?fuseaction=viewarticle&CO_ContentID=14895&channel=art&PageView=Search

http://www.systeminetwork.com/resources/artarchive/index.cfm?fuseaction=viewarticle&CO_ContentID=14940&channel=art&PageView=Search



Alan Shore

NBTY, Inc
(631) 244-2000 ext. 5019
AShore@xxxxxxxx


midrange-l-bounces@xxxxxxxxxxxx wrote on 12/06/2006 03:07:42 PM:

This is just a test.
I have responded to this original question a few times, but I have yet to
see my response posted.



Alan Shore

NBTY, Inc
(631) 244-2000 ext. 5019
AShore@xxxxxxxx

midrange-l-bounces@xxxxxxxxxxxx wrote on 12/06/2006 03:02:28 PM:

I assume you have the SQL licensed product - then you also have
Query Manager. Run the STRQM command, take option 1, if "Query
creation mode" says Prompt, press F19 to set it to SQL. Change the
library if you want. Put a 1 in the opt column and put in a name,
say MYQRY. Press enter.

Put the following statement into the editor:

select distinct * from mylib/myfile

At this point you don't even have to create the QMQRY - you can test
it using F5. The first time, choose display (1) for output, to see
that the result is what you want. Then exit and do F5 again - this
time choose file (3) for output - you will get a prompt for the
output file. Put in the same name as the one in the select statement
- mylib/myfile.

This will replace the contents of mylib/myfile with the result of
the SELECT statement - in place - with no warning. This is dangerous
- always make a backup first. Or verify as suggested above.

If you do not have the STRQM command, you can CRTSRCPF
yourlib/QQMQRYSRC rcdlen(91), add a member, put the above statement
into it, execute the CRTQMQRY command against that source member,
use STRQMQRY source to run the statement and choose the output type
you want - similar to above.

HTH
Vern
-------------- Original message --------------
From: "Jim Essinger" <dilbernator@xxxxxxxxx>

Greetings!

I have a file that, among other fields, has Account_Number, Date,

Sequence

number.

In the file there are multiple occurrences of those three fields.

Example


Account_Number Date Sequence_number
1 20061206 01
1 20061206 01
1 20061206 01
1 20061206 02
1 20061206 02
1 20061206 02
1 20061206 03
1 20061206 03
1 20061206 03
1 20061206 04
1 20061206 04
1 20061206 04

What I am looking for is a way to delete all but one occurrence of

the

repeated data to get a file that looks like;

Account_Number Date Sequence_number
1 20061206 01
1 20061206 02
1 20061206 03
1 20061206 04


Is this something I can do with SQL? If so, what would the SQL

statement

look like?


Jim

--
Jim Essinger
Senior Programmer/Analyst
SLFIMA
PO Box 1000
Fruitland, ID 83619
208-452-4058 x133
--
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.