CODEWITHSUNDEEP
sqlsql-server
Andulos
Andulos

Reputation: 475

How do I write a query to return specified number of rows with distinct values for a particular column?

I am looking to write a query to fetch 'n' number of rows with distinct values for the column PERSON_ID?

SELECT  
        HDR.PERSON_ID, DTL.DETAIL_ID
FROM 
        HEADER HDR, DETAIL DTL 
WHERE 
        HDR.HEADER_ID = DTL.HEADER_ID
    AND DTL.CODE = 'SUCCESS'
ORDER BY 
    HDR.PERSON_ID

enter image description here

If I do fetch top 5, the query will return rows 1 to 5. Instead I want to get back rows 1 to 7 - this will get me 5 person rows.

Upvotes: 0

Views: 47

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133400

You could try an inner join with the distinct PERSON_ID

  SELECT  HDR.PERSON_ID, DTL.DETAIL_ID
  FROM  HEADER HDR
  INNER JOIN  DETAIL DTL  ON HDR.HEADER_ID = DTL.HEADER_ID
      AND DTL.CODE = 'SUCCESS'
  INNER JOIN  (

    SELECT DISTINCT  HDR.PERSON_ID
    FROM  HEADER HDR
    INNER JOIN  DETAIL DTL  ON  HDR.HEADER_ID = DTL.HEADER_ID
      AND DTL.CODE = 'SUCCESS'
    ORDER BY   HDR.PERSON_ID

    ) T ON T.PERSON_ID = HDR.PERSON_ID
  ORDER BY   HDR.PERSON_ID

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You can use DENSE_RANK() to answer your question. More importantly, you can learn to use proper, explicit, standard JOIN syntax.

SELECT pd.PERSON_ID, pd.DETAIL_ID
FROM (SELECT HDR.PERSON_ID, DTL.DETAIL_ID,
             DENSE_RANK() OVER (ORDER BY HDR.PERSON_ID) as ranking
      FROM HEADER HDR JOIN
           DETAIL DTL 
           ON HDR.HEADER_ID = DTL.HEADER_ID AND DTL.CODE = 'SUCCESS'
     ) pd
WHERE ranking <= 5;

Upvotes: 3

Related Questions