# Eastern challenge

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!

## 14 Replies to “Eastern challenge”

1. So this is wrong, 85 characters:

SELECT*FROM(SELECT job,COUNT(*)FROM emp GROUP BY job ORDER BY COUNT(*))WHERE ROWNUM=1

2. Still 85 chars:

SELECT*FROM(SELECT job,RANK()OVER(ORDER BY COUNT(*))r FROM emp GROUP BY job)WHERE r=1

3. 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

4. select min(job)keep(dense_rank first order by count(*))from emp group by job

5. 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 ðŸ™‚ )

6. SELECT job FROM emp GROUP BY job having sum(1)<=all(select sum(1)from emp GROUP BY job)

7. yeah, sum(1) !!!

I should have used it too, making sten solution two chars smaller ðŸ™‚

8. 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 !

9. agree. Well, I said : “Using the well known EMP table” so the solution could be SELECT ‘PRESIDENT’ FROM DUAL ðŸ™‚

10. Hi, 72 for less than 100K employees companies ðŸ™‚

select substr(min(to_char(sum(1),’00000′)||job),7)from emp group by job

11. 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

12. Well done, replace 10e6 by 1e7 and remove the space after the parenthese and you will come to the impressive 58 ðŸ™‚

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