How to Select only one unique record from the Table using Distinct

Assuming you just want any row at random for each memberid than you can do this:

select memberid, this, that, theotherfrom(select memberid, this, that, theother,       row_number() over (partition by memberid order by this) rnfrom   annualfees)where rn = 1;

If you wanted a specific row per memberid, e.g. the one with the most recent StartDate then you could modify it to:

select memberid, this, that, theotherfrom(select memberid, this, that, theother,       row_number() over (partition by memberid order by StartDate desc) rnfrom   annualfees)where rn = 1;

don't know if this is quite what you need, but you may need to look at GROUP BY instead of DISTINCT...

if you have several records with the same member id, you may need to specify exaclty how to identify the one you want from the others

eg to get each member`s last starting date:

SELECT memberid, max(startingdate)FROM annualfeesGROUP BY memberid

but if you need to identify one record in this kind of way but also display the other columns, i think you may need to do some trickery like this...

eg sub-query the above SELECT with a join to join the other columns you want:

SELECT subq.memid, subq.startdate, a.expirydate, a.amountFROM (  SELECT memberid AS memid, max(startingdate) AS startdate  FROM annualfees  GROUP BY memberid ) subqINNER JOIN annualfees a ON a.memberid = subq.memid                AND a.startingdate = subq.startdate

from start to finish, also showing data table (o/p was traced/grabbed using "SET VERIFY ON")...

-- show all rowsselect *from annualfeesorder by memberid, startingdateMEMBERID               STARTINGDATE              EXPIRYDATE           AMOUNT               ---------------------- ------------------------- -------------------- -------------------- 1                      02-DEC-09                 05-FEB-10            111                  1                      25-JUN-10                 25-JUN-11            222                  2                      25-APR-10                 25-JUN-13            333                  3 rows selected/-- show one member`s data using max(startingdate) as selector.SELECT memberid, max(startingdate)    FROM annualfees    GROUP BY memberidMEMBERID               MAX(STARTINGDATE)         ---------------------- ------------------------- 1                      25-JUN-10                 2                      25-APR-10                 2 rows selected/ -- show above data joined with the other columns.SELECT subq.memid, subq.startdate, a.expirydate, a.amount    FROM (      SELECT memberid AS memid, max(startingdate) AS startdate      FROM annualfees      GROUP BY memberid ) subq    INNER JOIN annualfees a ON a.memberid = subq.memid AND a.startingdate = subq.startdateMEMID                  STARTDATE                 EXPIRYDATE           AMOUNT               ---------------------- ------------------------- -------------------- -------------------- 1                      25-JUN-10                 25-JUN-11            222                  2                      25-APR-10                 25-JUN-13            333                  2 rows selected/

You need to select which of the rows with duplicate MemberIDs to return in some way. This will get the row with the greatest startingDate.

SELECT MemberID,StartingDate,ExpiryDate,Amount FROM AnnualFees afWHERE NOT EXISTS (        SELECT * from AnnualFees af2         WHERE af2.MemberID = af.MemberID         AND af2.StartingDate > af.StartingDate)