Re: SQL query help

<krallabandi_at_gmail.com> wrote in message news:1156345979.825667.285440_at_h48g2000cwc.googlegroups.com
> The below query returns me
>
> SELECT Client_Structure_Version.CLIENT_STRUCTURE_ID,
> FIELD_NAME,
> FIELD_TYPE, FIELD_WIDTH, FIELD_ORDER
> FROM Client_Structure_Version
> ORDER BY client_structure_record_id,FIELD_ORDER;
>
> 1649,"RECORDCODE","Varchar",1,1
> 1649,"ACTIVITYIND","Varchar",1,2
> 1649,"MEMBERID","Varchar",9,3
> 1649,"FIRSTNAME","Varchar",20,4 (this record is having
> client_structure_record_id 1)
> 1649,"MIDDLEINITIAL","Varchar",20,5
> 1649,"LASTNAME","Varchar",30,6
> 1649,"SEX","Varchar",1,7
> 1649,"BIRTHDATE","Varchar",8,8
> 1649,"ADDRESS1","Varchar",30,9
> 1649,"ADDRESS2","Varchar",30,10
> 1649,"ADDRESS3","Varchar",30,11
> 1649,"CITY","Varchar",20,12
> 1649,"STATE","Varchar",2,13
> 1649,"ZIP","Varchar",5,14
> 1649,"ZIP2","Varchar",4,15
> 1649,"FILLER1","Varchar",41,16
> 1649,"EFFDATE","Varchar",8,18
> 1649,"TERMDATE","Varchar",8,19
> 1649,"PLANTYPE","Varchar",3,21
> 1649,"PLANCODE","Varchar",3,22
> 1649,"OPTION","Varchar",2,23
> 1649,"GROUP","Varchar",20,24
> 1649,"FILLER2","Varchar",304,25
> 1649,"RECORDCODE_S","Varchar",1,1
> 1649,"MEMBERID_S","Varchar",9,2
> 1649,"ssn_S","Varchar",9,3
> 1649,"FIRSTNAME","Varchar",20,4 (this record is having
> client_structure_record_id 2)
> 1649,"MIDDLEINITIAL_S","Varchar",20,5
> 1649,"LASTNAME_S","Varchar",30,6
> 1649,"SEX_S","Varchar",1,7
> 1649,"BIRTHDATE_S","Varchar",8,8
> 1649,"RELCODE_S","Varchar",1,9
> 1649,"FILLER2_S","Varchar",4,10
> 1649,"EFFDATE_S","Varchar",8,11
> 1649,"TERMDATE_S","Varchar",8,12
> 1649,"ADDRESS1_S","Varchar",50,14
> 1649,"ADDRESS2_S","Varchar",50,16
> 1649,"ADDRESS3_S","Varchar",50,17
> 1649,"CITY_S","Varchar",20,19
> 1649,"STATE_S","Varchar",20,20
> 1649,"ZIP_S","Varchar",5,21
> 1649,"ZIP2_S","Varchar",4,22
> 1649,"FILLER3_S","Varchar",282,30
>
> I don't want the repetetions. so I modified the query as
>
> SELECT distinct * from (SELECT
> Client_Structure_Version.CLIENT_STRUCTURE_ID, FIELD_NAME,
> FIELD_TYPE, FIELD_WIDTH, FIELD_ORDER
> FROM Client_Structure_Version
> ORDER BY client_structure_record_id,FIELD_ORDER);
>
> The above query has taken out the duplications. but It also changed
> the order. I don't want the order to be changed.
>
> 1649,"ACTIVITYIND","Varchar",1,2
> 1649,"ADDRESS1","Varchar",30,9
> 1649,"ADDRESS1_S","Varchar",50,14
> 1649,"ADDRESS2","Varchar",30,10
> 1649,"ADDRESS2_S","Varchar",50,16
> 1649,"ADDRESS3","Varchar",30,11
> 1649,"ADDRESS3_S","Varchar",50,17
> 1649,"BIRTHDATE","Varchar",8,8
> 1649,"BIRTHDATE_S","Varchar",8,8
> 1649,"CITY","Varchar",20,12
> 1649,"CITY_S","Varchar",20,19
> 1649,"EFFDATE","Varchar",8,18
> 1649,"EFFDATE_S","Varchar",8,11
> 1649,"FILLER1","Varchar",41,16
> 1649,"FILLER2","Varchar",304,25
> 1649,"FILLER2_S","Varchar",4,10
> 1649,"FILLER3_S","Varchar",282,30
> 1649,"FIRSTNAME","Varchar",20,4
> 1649,"GROUP","Varchar",20,24
> 1649,"LASTNAME","Varchar",30,6
> 1649,"LASTNAME_S","Varchar",30,6
> 1649,"MEMBERID","Varchar",9,3
> 1649,"MEMBERID_S","Varchar",9,2
> 1649,"MIDDLEINITIAL","Varchar",20,5
> 1649,"MIDDLEINITIAL_S","Varchar",20,5
> 1649,"OPTION","Varchar",2,23
> 1649,"PLANCODE","Varchar",3,22
> 1649,"PLANTYPE","Varchar",3,21
> 1649,"RECORDCODE","Varchar",1,1
> 1649,"RECORDCODE_S","Varchar",1,1
> 1649,"RELCODE_S","Varchar",1,9
> 1649,"SEX","Varchar",1,7
> 1649,"SEX_S","Varchar",1,7
> 1649,"STATE","Varchar",2,13
> 1649,"STATE_S","Varchar",20,20
> 1649,"TERMDATE","Varchar",8,19
> 1649,"TERMDATE_S","Varchar",8,12
> 1649,"ZIP","Varchar",5,14
> 1649,"ZIP2","Varchar",4,15
> 1649,"ZIP2_S","Varchar",4,22
> 1649,"ZIP_S","Varchar",5,21
> 1649,"ssn_S","Varchar",9,3
>
> Is there any way to get the results of query 2 with order from query
> 1?
>
> I appreciate your help.
>
> Thanks

What you're asking doesn't make sense. By merging records you eliminated "client_structure_record_id" information, and now you want the new recordset ordered by it. Take for example "FIRSTNAME" record (one instance after merging), what is its client_structure_record_id to you: 1 or 2? Received on Thu Aug 24 2006 - 18:11:43 CEST