-1

Suppose I've Table T and It has 3 Columns and 3rd Column contains 10 distinct values and I want to fetch 5 samples from Table T for each distinct value in column 3rd.

enter image description here

Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
Yogendra Singh Dhakad
  • 1
  • 1
  • 1
    What is your MySQL server version ? Also, pics are not good way to post SQL related questions. Ref: [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/q/333952/2469308) – Madhur Bhaiya Oct 25 '18 at 12:58
  • Please don't post sample data as image as it is not always accessible to all, post it as text format with sample output would be more helpful. – Yogesh Sharma Oct 25 '18 at 12:59
  • Possible duplicate of [Top N per Group Sql problem in mysql](https://stackoverflow.com/questions/7133574/top-n-per-group-sql-problem-in-mysql) – Ankit Bajpai Oct 25 '18 at 13:08
  • 2
    Possible duplicate of [Using LIMIT within GROUP BY to get N results per group?](https://stackoverflow.com/questions/2129693/using-limit-within-group-by-to-get-n-results-per-group) – Kirween Oct 25 '18 at 13:11

1 Answers1

0
WITH T(A, B,C) AS(
SELECT 12,'Hi','One' FROM DUAL UNION ALL
SELECT 34,'Am','One' FROM DUAL UNION ALL
SELECT  6,'to','Two' FROM DUAL UNION ALL
SELECT  3,'do','Two' FROM DUAL UNION ALL
SELECT  5,'aim','Two' FROM DUAL UNION ALL
SELECT  6,'mine','Two' FROM DUAL UNION ALL
SELECT  4,'not','Three' FROM DUAL 
)
select a,b,c from(
SELECT A,B,C,ROW_NUMBER() OVER(PARTITION BY c ORDER BY C DESC) AS R
FROM T)
where r = 1

or you could possibly try this:

 SELECT MIN(T.A),  
         T.B, 
         t.c
    FROM t
    JOIN (SELECT C,
                 MAX(b) AS b
            FROM T
        GROUP BY C) Y ON Y.C = T.C
                     AND Y.B = T.B
GROUP BY t.c, t.b
W_O_L_F
  • 1,049
  • 1
  • 9
  • 16