-6

I made a table which contain:

 sid| bid |date
 ---+-----+---------
 31 | 102 |2002-05-09
 31 | 103 |2002-05-09
 31 | 104 |2002-09-22
 22 | 101 |2002-10-10
 22 | 102 |2002-10-10
 22 | 103 |2003-01-10
 64 | 101 |2003-01-11
 64 | 102 |2003-01-11
 22 | 104 |2003-02-10
 74 | 103 |2003-02-12

I want to show the less frequent id which is 101 and 104. I found out how to make the most frequent but how to shot the least frequent?

Dushmantha
  • 2,911
  • 1
  • 14
  • 21
Bagito Samari
  • 1
  • 5
    What have you got for most frequent? What MySQL version? – danblack Jan 14 '19 at 05:29
  • 1
    @SanSolo this is not duplication of that link because OP want two ids value 101 and 104 (not only 1 row by sorting and limit 1), which come to a `RANK()` or `DENSE_RANK()` function (in other dbms). MySql would have some workaround to achieve that. – Pham X. Bach Jan 14 '19 at 06:55

1 Answers1

0

Maybe I can do something more optimized but here it is 

CREATE TABLE work (sid int,bid int,`date` datetime);

INSERT INTO work (sid,bid,date)
VALUES
(31,102,'2002-05-09'),
(31,103,'2002-05-09'),
(31,104,'2002-09-22'),
(22,101,'2002-10-10'),
(22,102,'2002-10-10'),
(22,103,'2003-01-10'),
(64,101,'2003-01-11'),
(64,102,'2003-01-11'),
(22,104,'2003-02-10'),
(74,103,'2003-02-12');


SELECT @min :=MIN(nr) FROM 
(SELECT bid,count(*) as nr FROM work GROUP BY bid) a;

SELECT @min;


SELECT bid FROM (
SELECT bid,count(*) as nr FROM work GROUP BY bid HAVING count(*)=@min) a;
Alexandru Banu
  • 1
  • 1