RE: how to retrieve records and make sure they arrive in RRN sequence

My objective is to keep the records in the same sequence that they exist in the file - ie ARRIVAL SEQ. Will ROW_NUMBER() get me there??



-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Tom E Stieger
Sent: Tuesday, February 12, 2013 5:50 PM
To: Midrange Systems Technical Discussion
Subject: RE: how to retrieve records and make sure they arrive in RRN sequence

Is there a reason not to use the ROW_NUMBER() OVER(PARTITION BY ... ORDER BY ... ) function?

create table qtemp/memotext as
(
With ALLDATA as (select * from mdcmdct
union
select * from mdcmtx1)
Select ROW_NUMBER() OVER(), ALLDATA.* from ALLDATA
ORDER BY ROW_NUMBER() OVER()
) with data;

You will want to place your ordering criteria within the OVER to determine how it should number. Also, your ROW_NUMBER() doesn't have to be in the selection criteria.


-Tom Stieger
IT Manager
California Fine Wire





-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Stone, Joel
Sent: Tuesday, February 12, 2013 2:33 PM
To: 'Midrange Systems Technical Discussion'
Subject: SQL: how to retrieve records and make sure they arrive in RRN sequence

Normally records seem to arrive in RRN sequence - if no other ORDER BY is stated.

However, when I use UNION to concat two SELECT results together, they are arriving in a random sequence.

How can I assure that the result set is in RRN seq?

However, I don't think I can include RRN in the SELECT because that field is NOT in the result table.

Thanks



create table qtemp/memotext as
(select * from mdcmdct
union
select * from mdcmtx1

) with data;

______________________________________________________________________
This outbound email has been scanned for all viruses by the MessageLabs Skyscan service.
For more information please visit http://www.symanteccloud.com ______________________________________________________________________
--
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.