Column with Duplicate Values
Query Manager Not Returning Duplicate Values in a Column - Below is my SQL - I have duplicate records set to only return copy of first row. This works fine - except when the column C. PC_FMV has two records with the same value - only returns one and I need both.
SELECT DISTINCT -- Columns B.PP_DISTRICT_CODE, A.RD_STATUS_YYMMDD, D.N_ID_NR, A.RD_PARCEL_NO, D.N_NAME, C.PC_YEAR, D.N_HOUSE_NR, D.N_STREET, D.N_ADDR2, D.N_CITY, D.N_STATE, D.N_ZIP_FIRST_FIVE, B.PP_NO_MH_W_O_FOUNDATION, B.PP_NO_BOATS, C.PC_FMV -- Tables FROM "QS36F"/"T16.CRDC" A, "QS36F"/"T16.PPAR" B, "QS36F"/"T16.PCTG" C, "QS36F"/"T16.GNAM" D -- Row Selection WHERE ((A.RD_STATUS_YYMMDD > 20110801) AND (B.PP_TOWNSHIP = 1159) AND (A.RD_ROLL_TYPE = 'P') AND NOT(C.PC_RECORD_STATUS = 'D'))
AND (A.RD_YEAR = B.PP_YEAR) AND (A.RD_PP_SHORT_KEY = B.PP_SHORT_KEY) AND (B.PP_YEAR = C.PC_YEAR) AND (B.PP_SHORT_KEY = C.PC_SHORT_KEY) AND (B.PP_PRIMARY_TAXPAYER = D.N_ID_NR) Sort Columns ORDER BY A.RD_STATUS_YYMMDD, D.N_ID_NR, A.RD_PARCEL_NO, D.N_NAME, C.PC_YEAR, B.PP_NO_MH_W_O_FOUNDATION, B.PP_NO_BOATS, C.PC_FMV
Software/Hardware used:
AS400
SELECT DISTINCT -- Columns B.PP_DISTRICT_CODE, A.RD_STATUS_YYMMDD, D.N_ID_NR, A.RD_PARCEL_NO, D.N_NAME, C.PC_YEAR, D.N_HOUSE_NR, D.N_STREET, D.N_ADDR2, D.N_CITY, D.N_STATE, D.N_ZIP_FIRST_FIVE, B.PP_NO_MH_W_O_FOUNDATION, B.PP_NO_BOATS, C.PC_FMV -- Tables FROM "QS36F"/"T16.CRDC" A, "QS36F"/"T16.PPAR" B, "QS36F"/"T16.PCTG" C, "QS36F"/"T16.GNAM" D -- Row Selection WHERE ((A.RD_STATUS_YYMMDD > 20110801) AND (B.PP_TOWNSHIP = 1159) AND (A.RD_ROLL_TYPE = 'P') AND NOT(C.PC_RECORD_STATUS = 'D'))
AND (A.RD_YEAR = B.PP_YEAR) AND (A.RD_PP_SHORT_KEY = B.PP_SHORT_KEY) AND (B.PP_YEAR = C.PC_YEAR) AND (B.PP_SHORT_KEY = C.PC_SHORT_KEY) AND (B.PP_PRIMARY_TAXPAYER = D.N_ID_NR) Sort Columns ORDER BY A.RD_STATUS_YYMMDD, D.N_ID_NR, A.RD_PARCEL_NO, D.N_NAME, C.PC_YEAR, B.PP_NO_MH_W_O_FOUNDATION, B.PP_NO_BOATS, C.PC_FMV
Software/Hardware used:
AS400
ASKED:
January 9, 2012 8:56 PM
UPDATED:
February 28, 2012 12:09 PM
Answer Wiki:
This doesn't fit too good - but the numbers beginning with 159 are from the FMV column. There are two records with the same amount 5880 - it only returns one record because the value is the same. How can I get around this without having it generate all duplicates?
Dist Status Name Id
Code Date Number Parcel Name Year House Nr Street Address line 2 City St Zip # MH # Boats FMV
---- ---------- ------- ------- ------------------------------ ---- ---------- ----------------- -------------------- --------------- -- ----- ---- ------- ----------
1159 2011/08/23 378207 0007127 BARRIER ISLAND INC 2011 328 FRONT ST BEAUFORT NC 28516 0 7 50
0 7 159
0 7 2100
0 7 5880
0 7 9180
0 7 9286
0 7 11360
0 7 19344
0 7 54164
----------
111523
==========
111523
To see all answers submitted to the Answer Wiki: View Answer History.
If the duplicate data in C.PC_FMV causes the whole returned record to be duplicate, then the DISTINCT keyword will remove it from the result. But I’m not sure if that is what you meant. If not, I would recommend posting some example data with its expected results.
…when the column C. PC_FMV has two records with the same value…
As Carlosdl says, DISTINCT is going to eliminate duplicate rows. That’s one of its fundamental purposes.
If you need those two rows both to show up, you will probably need to include something else from “QS36F”/”T16.PCTG” C that distinguishes the two rows. One complication is that any additional column from that file might cause every row to be unique which could mess up the rest of DISTINCT.
You might try creating something like a DISTINCT VIEW over the other three files without including “QS36F”/”T16.PCTG” C. And then use that VIEW to join to “QS36F”/”T16.PCTG” C. That would effectively be a ‘table expression’ which is a possible alternative. The VIEW (or ‘table expression’) would be DISTINCT by itself; but when joined to “QS36F”/”T16.PCTG” C, it could give duplicates in the final result set. The DISTINCT should apply only to the three files in the VIEW.
Without knowing the possible data, it’s hard to say whether that would work or not.
Tom
Answer Wiki:
This doesn’t fit too good – but the numbers beginning with 159 are from the FMV column. There are two records with the same amount 5880 – it only returns one record because the value is the same. How can I get around this without having it generate all duplicates?
Dist Status Name Id
Code Date Number Parcel Name Year House Nr Street Address line 2 City St Zip # MH # Boats FMV
—- ———- ——- ——- —————————— —- ———- —————– ——————– ————— — —– —- ——- ———-
1159 2011/08/23 378207 0007127 BARRIER ISLAND INC 2011 328 FRONT ST BEAUFORT NC 28516 0 7 50
0 7 159
0 7 2100
0 7 5880
0 7 9180
0 7 9286
0 7 11360
0 7 19344
0 7 54164
———-
111523
==========
111523