"SQL Test Question"
From the PL/SQL Pipelines

Hello guys, long time no see

Brother send me a simple test question:

There is table (number,date), show first 3 minimal dates for every number.

Need to find shortest and simplest solution (one SQL query).

Any ideas?

Thanks,

Vladimir Trusevich
Programmer Analyst

 

I don't see how it's possible

Ravi

select num,dte from
(select rownum rnum,num,dte
from tbl
order by dte)
where rnum<=3;
- Dan Clamage
Download free, cool PL/SQL utilities and source code at:
http://www.clamage.com  

Sorry, I didn't mention that query should work on 7.3 (where you can't do ORDER BY in subquery).

Any ideas?

Thanks,

Vladimir Trusevich
Programmer Analyst

 

>select num,dte from
>(select rownum rnum,num,dte
>from tbl
>order by dte)
>where rnum<=3;

Anyway, this will gave us only 3 first rows, not 3 first rows for every number.

Thanks,

Vladimir Trusevich
Programmer Analyst

I can't seem to do it in SQL. You may have to use PL/SQL. Perhaps a packaged function callable from SQL will enable you do it as a "pure" SQL.
- Dan Clamage
Download free, cool PL/SQL utilities and source code at:
http://www.clamage.com  

I don't believe no one of gurus can suggest simple solution for simple "top-n for every key" question?!

To save your time I'll repeat question:

from table(number,date) need to find 3 min(or max, whatever) dates for every number key.

Thanks,

Vladimir Trusevich
Beginner SQL student 

This requires a control break on num, for which SQL isn't really suited. I've always implemented control breaks in procedural logic.
That's my final answer.
- Dan Clamage
Download free, cool PL/SQL utilities and source code at:
http://www.clamage.com

This requires a control break on num, for which SQL isn't really suited. I've always implemented control breaks in procedural logic. That's my final answer.

Dan,
Totally agree with you. Well, I was. Until Darko came with solution. Probably we became to old for that stuff, hm?

So final query for 3 min dates looks like that:

select n, d

from test t1

where 3 >=

  (select count(*)

   from test t2

   where t2.n=t1.n and t2.d<= t1.d)

order by 1,2

Unbelievable, but it works!!!
It's only works, if D is unique set for every N, and query will be slow for big table, but that's not important.

Thanks Darko, I always new there should be somebody to solve unsolvable.

Vladimir Trusevich
Programmer Analyst

 

select * from tbl;

       NUM DTE      

---------- ---------

         4 01-APR-00

         4 01-MAR-00

         4 01-MAY-00

         2 01-MAY-00

         2 01-APR-00

         2 01-MAR-00

         6 01-MAR-00

         6 01-MAY-00

         6 01-APR-00

         4 01-JAN-00

         6 01-JAN-00

         2 01-JAN-00

select num, dte

from tbl t1

where 3 >=

  (select count(*)

   from tbl t2

   where t2.num=t1.num and t2.dte<= t1.dte)

order by 1,2

/

       NUM DTE      

---------- ---------

         2 01-JAN-00

         2 01-MAR-00

         2 01-APR-00

         4 01-JAN-00

         4 01-MAR-00

         4 01-APR-00

         6 01-JAN-00

         6 01-MAR-00

         6 01-APR-00

I have been humbled.
I must've gotten that one wrong on the test. I sure didn't remember it. Of course, that was 12 yrs ago. Time to take a refresher course!
- Dan Clamage
Download free, cool PL/SQL utilities and source code at:
http://www.clamage.com

If I still remember, it was a question in my first PL/SQL course. It should be something like:
select my_num_col, my_date_col
from my_table a
where 2<= (select count(*) from my_table b
where b.my_num_col=a.my_num_col
and b.my_date_col<= a.my_date_col)
order by 1,2 desc

Change :
b.my_date_col<= a.my_date_col
in
b.my_date_col>=a.my_date_col
for 3 minimal values.
d.e.