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 1)
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

Sorry for cut/paste mistake.

In my first query , FIRSTNAME is repeated because it is having
client_structure_record_id = 1 for the first record and
client_structure_record_id = 2 for the second record

Thanks,

Sorry my mistake. Original query is

query1:

SELECT Client_Structure_Version.CLIENT_STRUCTURE_ID, FIELD_NAME,
FIELD_TYPE, FIELD_WIDTH, FIELD_ORDER
FROM Client_Structure_Version
Inner Join Client_Structure
ON Client_Structure_Version.Client_Structure_ID =
Client_Structure.Client_Structure_ID
WHERE Client_ID = 556 AND
effective_date = ( SELECT MAX(Effective_Date) FROM
Client_Structure_Version WHERE Client_ID = 556)
AND discontinue_date IS NULL
ORDER BY client_structure_record_id,FIELD_ORDER

query 2:

select distinct * from (
SELECT Client_Structure_Version.CLIENT_STRUCTURE_ID, FIELD_NAME,
FIELD_TYPE, FIELD_WIDTH, FIELD_ORDER
FROM Client_Structure_Version
Inner Join Client_Structure
ON Client_Structure_Version.Client_Structure_ID =
Client_Structure.Client_Structure_ID
WHERE Client_ID = 556 AND
effective_date = ( SELECT MAX(Effective_Date) FROM
Client_Structure_Version WHERE Client_ID = 556)
AND discontinue_date IS NULL
ORDER BY client_structure_record_id,FIELD_ORDER)

Thanks

any ideas??

KK schrieb:

> any ideas??

There's no need to put an extra select around your original select.
Just add distinct to your original select statement and everything
should work as expected.

Regards,

Jörg