Oracle SQL: помогите с простым вопросом

[Komissar]

Такая таблица:
MYTABLE:
id number(8) unique not null (primary key)
prod_id number(8) not null
mod_date date

пример:

ID prod_id date
1 5 10-oct-03
2 6 13-oct-03
3 5 14-oct-03
4 6 16-oct-03

т.е. каждый prod_id может иметь несколько строк в таблице, с разными дате/тайм стампами.
нужно одним сиквелом получить по _одной_ строке для каждого продукта (prod_id), а именно по строке с самой свежей датой.

Т.е. из примера вверху, надо получить:

3 5 14-oct-03
4 6 16-oct-03

pomogite please

[sp123]

Самое простое:

SELECT id, prod_id, mod_date FROM mytable t
WHERE mod_date =
(SELECT MAX(mod_date) FROM mytable WHERE prod_id=t.prod_id)

[Komissar]

tx
Я в своем творении из-за опечатки залинковал свой суб-селект на id вместо prod_id, сейчас дошло, в чем дело.

А можно обойтись без суб-селекта? Просто интересно...

[sp123]

Формально не суб-селект :

Code: Select all

SELECT id, prod_id, mod_date FROM
(
 SELECT id, prod_id, mod_date, 
  row_number() over (PARTITION BY prod_id ORDER BY mod_date DESC) rn FROM mytable
)
WHERE rn = 1

[vc]

tx
Я в своем творении из-за опечатки залинковал свой суб-селект на id вместо prod_id, сейчас дошло, в чем дело.

А можно обойтись без суб-селекта? Просто интересно...

Unfortunately, no, you cannot.

If you want to get rid of the correlated subquery, then you can do this:

Code: Select all

select t1.id, t1.prod_id, t1.mod_date 
from mytable t1, 
     (select prod_id, max(mod_date) mod_date 
     from mytable
     group by prod_id) t2
where t1.prod_id=t2.prod_id
  and t1.mod_date=t2.mod_date

... or this:

Code: Select all

select id, prod_id, mod_date 
from mytable
where (prod_id, mod_date) in ( select prod_id, max(mod_date) from mytable group by prod_id)

Under the RBO, the two queries above should be faster than original sp123's response. Under the CBO, all three, however, will generate comparable plans.

However, the fastest one will be the analytical query sp123 posted later (it does contain a subquery, strictly speaking) as it will result in only one full table scan. The only reason not to use it would be the case when you do not have Oracle EE.


Rgds.

[Komissar]

А разве в последнем предложенном случае не случится ошибка, once number of items in the “in” clause exceeds 1000 (in Oracle)?

Мне доводилось встречать случаи, когда запрос с чем-то вроде:
select aaa from zzz where xxx in (select mmm from yyy where ...)

начинал возвращать ошибки, когда база выростала, и число items во втором селекте превосходило 1000...

Что по этому поводу думают гуру? Как свободно можно использовать запросы внутри IN?

[tengiz]

Как свободно можно использовать запросы внутри IN?

Такой IN имеет функциональный аналог в виде EXISTS, у которого не будет этой чудацкой проблемы. И для одного, и для другого существует функционально эквивалентный JOIN (который, правда, не имеет SQL аналога). Хороший оптимизатор вполне может применить ту или иную трансформацию. А иметь ограничение на количество строк в случае JOIN - это просто абсурд.

[vc]

А разве в последнем предложенном случае не случится ошибка, once number of items in the “in” clause exceeds 1000 (in Oracle)?

Мне доводилось встречать случаи, когда запрос с чем-то вроде:
select aaa from zzz where xxx in (select mmm from yyy where ...)

начинал возвращать ошибки, когда база выростала, и число items во втором селекте превосходило 1000...

Что по этому поводу думают гуру? Как свободно можно использовать запросы внутри IN?

Never seen this one -- please post a test case.

Rgds