RE: Extracting distinct rows into a new file via SQL

Requires V5R2 at least

-------------- Original message -------------- 
From: "Mark Walter" <mwalter@xxxxxxxxxxx> 

> CREATE TABLE MyNewTbl as (SELECT DISTINCT myField FROM MyOldTbl) WITH 
> DATA 
> 
> 
> Mark Walter 
> Senior Programmer/Analyst 
> IBM Certified RPG Developer 
> Hainey Business Systems 
> (717) 771-2362 
> mwalter@xxxxxxxxxxx 
> http://www.hbs-inc.com 
> 
> 
> -----Original Message----- 
> From: midrange-l-bounces@xxxxxxxxxxxx 
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of 
> daparnin@xxxxxxxxxxxxxx 
> Sent: Monday, March 20, 2006 11:19 AM 
> To: midrange-l@xxxxxxxxxxxx 
> Subject: Extracting distinct rows into a new file via SQL 
> 
> 
> 
> 
> 
> Can I use SQL to create a table from another table but without duplicate 
> records? I've got a one-field file that has numbers like 
> 140645 
> 140647 
> 140647 
> 140647 
> 140648 
> 140648 
> 
> I can do a SELECT DISTINCT * FROM ORDERLIST to get 
> 140645 
> 140647 
> 140648 
> 140649 
> but I would like to either put these into a new file or delete the dups 
> from the original file. Any thoughts would be appreciated. 
> 
> 
> Dave Parnin 
> -- 
> Nishikawa Standard Company 
> Topeka, IN 46571 
> daparnin@xxxxxxxxxxxxxx 
> 
> -- 
> 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. 
>