Eastern challenge

14 thoughts on “Eastern challenge”

1. Colin 't Hart April 9, 2009 at 15:45

   So this is wrong, 85 characters:

   SELECT*FROM(SELECT job,COUNT(*)FROM emp GROUP BY job ORDER BY COUNT(*))WHERE ROWNUM=1
2. Laurent Schneider Post authorApril 9, 2009 at 16:06

   76 must be your objective …
3. Colin 't Hart April 9, 2009 at 16:10

   Still 85 chars:

   SELECT*FROM(SELECT job,RANK()OVER(ORDER BY COUNT(*))r FROM emp GROUP BY job)WHERE r=1
4. Colin 't Hart April 9, 2009 at 16:14

   OK… 67… but it does print it out 5 times… once for each job 🙂

   SELECT FIRST_VALUE(job)OVER(ORDER BY COUNT(*))FROM emp GROUP BY job
5. Sten Vesterli April 9, 2009 at 16:39

   select min(job)keep(dense_rank first order by count(*))from emp group by job
6. Laurent Schneider Post authorApril 9, 2009 at 16:50

   well done all!
   I first add Sten solution in mind, but Colin first solution could be a 77
   SELECT*FROM(SELECT job FROM emp GROUP BY job ORDER BY COUNT(*))WHERE ROWNUM=1

   and Colin first_value could be a 78 by adding unique
   SELECT unique FIRST_VALUE(job)OVER(ORDER BY COUNT(*))FROM lsc_emp GROUP BY job

   (unique is a rare synonym of distinct, but shorter to type 🙂 )
7. Tegiri Nenashi April 10, 2009 at 00:20

   SELECT job FROM emp GROUP BY job having sum(1)<=all(select sum(1)from emp GROUP BY job)
8. Laurent Schneider Post authorApril 10, 2009 at 08:11

   yeah, sum(1) !!!

   I should have used it too, making sten solution two chars smaller 🙂
9. Sokrates April 12, 2009 at 09:56

   nice and great fun !

   but the question is wrong (at least, there is something missing):
   what, if there is more than one jobs that are ‘the less common’ ?

   sokrates > select job from emp;

   JOB
   ———
   bunny
   bunny
   easter
   easter

   4 rows selected.

   which of them should the query return ?
   all ?
   a randomly chosen ?
   none ?

   happy easterday to you !
10. Laurent Schneider Post authorApril 12, 2009 at 10:19

    agree. Well, I said : “Using the well known EMP table” so the solution could be SELECT ‘PRESIDENT’ FROM DUAL 🙂
11. Pacman December 2, 2011 at 16:44

    Hi, 72 for less than 100K employees companies 🙂

    select substr(min(to_char(sum(1),’00000′)||job),7)from emp group by job
12. Pacman December 2, 2011 at 17:27

    Actually we can do 61 chars for up to 90 millions employees 🙂

    select substr(min(10e6+sum(1)||job),9) from emp group by job
13. Laurent Schneider Post authorDecember 2, 2011 at 17:36

    Well done, replace 10e6 by 1e7 and remove the space after the parenthese and you will come to the impressive 58 🙂
14. Pacman December 2, 2011 at 17:45

    @Laurent Schneider
    Haha, i randomly tested the “e” version after the first fail 10^6… and noticed there was an unexpected additionnal 0 🙂

Comments are closed.