select b.species,a.sample_id,a.collect_date
from(
select *
from t_specimen
order by t_specimen.collect_date desc) a, t_specie_dict b
where a.sample_id like '%'||b.species||'%' ||rownum<3;
from(
select *
from t_specimen
order by t_specimen.collect_date desc) a, t_specie_dict b
where a.sample_id like '%'||b.species||'%' ||rownum<3;
select species,sample_id,collect_date,
ROW_NUMBER()
OVER (
PARTITION BY species ORDER BY rn asc
) Top2
from
(
select b.species,a.sample_id,a.collect_date,rownum rn
from(
select *
from t_specimen
order by t_specimen.collect_date desc) a, t_specie_dict b
where a.sample_id like '%'||b.species||'%'
) tb where Top2 <= 2
具体参见:
http://www.akadia.com/services/ora_analytic_functions.html