select only distinct value from one column

Question

I am displaying three fields from my query, and I want to display a distinct banking_no. I still want to display the other fields even they are not distinct. Please help.

SELECT  C.RECEIPT_OFFICE_PREFIX, B.BANKING_NO, B.STATUS_CD
FROM    TControl B, TComponent C
WHERE   C.DEPOSIT_BANK_ACCT = 'xxx-xxxxxx-xxxxx'
AND     B.BANKING_NO = C.BANKING_NO
AND     B.COMPANY_ID = C.COMPANY_ID
AND     B.RECEIPT_OFFICE_PREFIX = C.RECEIPT_OFFICE_PREFIX
AND     B.STATUS_CD != 'C'
ORDER BY B.BANKING_NO

I am using Sybase ASE 12.5

Answer 1

More recent versions of Sybase support row_number(). You would use it in a subquery like this:

SELECT RECEIPT_OFFICE_PREFIX, BANKING_NO, STATUS_CD
FROM (SELECT C.RECEIPT_OFFICE_PREFIX, B.BANKING_NO, B.STATUS_CD,
             ROW_NUMBER() OVER (PARTITION BY B.BANKING_NO ORDER BY B.BANKING_NO) as seqnum
      FROM TControl B JOIN
           TComponent C
           ON B.BANKING_NO = C.BANKING_NO AND B.COMPANY_ID = C.COMPANY_ID
      WHERE C.DEPOSIT_BANK_ACCT = 'xxx-xxxxxx-xxxxx' AND     
            B.RECEIPT_OFFICE_PREFIX = C.RECEIPT_OFFICE_PREFIX AND
            B.STATUS_CD != 'C'
    ) BC
WHERE seqnum = 1
ORDER BY BANKING_NO;