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

Question

my table have several records which has the same MemberID. i want to result out only one record.

select DISTINCT(MemberID)  from AnnualFees;

then result will come. but i want to show the other column data also but when i do this

select DISTINCT(MemberID),StartingDate,ExpiryDate,Amount  from AnnualFees;

all the details including same MemberID data also displaying.

can someone help me.

Answer 1

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

select memberid, this, that, theother
from
(
select memberid, this, that, theother,
       row_number() over (partition by memberid order by this) rn
from   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, theother
from
(
select memberid, this, that, theother,
       row_number() over (partition by memberid order by StartDate desc) rn
from   annualfees
)
where rn = 1;

Answer 2

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 annualfees
GROUP 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.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.startdate

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

-- show all rows
select *
from annualfees
order by memberid, startingdate
MEMBERID               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 memberid
MEMBERID               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.startdate
MEMID                  STARTDATE                 EXPIRYDATE           AMOUNT               
---------------------- ------------------------- -------------------- -------------------- 
1                      25-JUN-10                 25-JUN-11            222                  
2                      25-APR-10                 25-JUN-13            333                  

2 rows selected

/

Answer 3

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 af
WHERE NOT EXISTS (
        SELECT * from AnnualFees af2 
        WHERE af2.MemberID = af.MemberID 
        AND af2.StartingDate > af.StartingDate)