Eastern challenge
April 9th, 2009
I just wanted to give to my readers a bit fun for Eastern…
Using the well known EMP table, retrieve the name of the job that is the less common…
The solution must be a one-liner SELECT statement (max 80 chars) 
Have fun!
So this is wrong, 85 characters:
SELECT*FROM(SELECT job,COUNT(*)FROM emp GROUP BY job ORDER BY COUNT(*))WHERE ROWNUM=1
76 must be your objective …
Still 85 chars:
SELECT*FROM(SELECT job,RANK()OVER(ORDER BY COUNT(*))r FROM emp GROUP BY job)WHERE r=1
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
select min(job)keep(dense_rank first order by count(*))from emp group by job
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
)
SELECT job FROM emp GROUP BY job having sum(1)<=all(select sum(1)from emp GROUP BY job)
yeah, sum(1) !!!
I should have used it too, making sten solution two chars smaller
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 !
agree. Well, I said : “Using the well known EMP table” so the solution could be SELECT ‘PRESIDENT’ FROM DUAL