Column with Duplicate Values

lorit
365 pts.
Tags:
Column Duplicates
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
Asked: Last updated:
Related Questions

Answer Wiki

Thanks. We'll let you know when a new response is added.

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

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • carlosdl
    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.
    84,495 pointsBadges:
    report
  • TomLiotta
    ...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
    125,585 pointsBadges:
    report
  • lorit
    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
    365 pointsBadges:
    report
  • The Most-Watched IT Questions: January 18, 2012 - ITKE Community Blog
    [...] 8. Carlosdl, TomLiotta, and Lorit give their ideas about columns with duplicate values. [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: