Преобразование rownum из Oracle в Postgres

Question

Мне нужно сделать преобразование из Oracle SQL в PostgreSQL.

  select * from table1 inner join table2 on table1.id = table2.table1Id
  where table1.col1 = 'TEST' 
  and rownum <=5
  order by table2.col1

Если я удалю and rownum <=5 и положить в конце limit 5Есть различия между двумя диалектами. В Oracle сначала выбираются 5 элементов, а после этого они сортируются по table2.col1,
В Postgres сначала сортируется весь список, и ПОСЛЕ того, как выбираются первые 5 элементов.

Как я могу получить тот же результат в Postgres, что и в Oracle?

Спасибо!

Answer 1

В зависимости от версии, которую вы используете, PostgreSQL 8.4 и выше имеют функции Window. Оконная функция ROW_NUMBER() способна реализовать функциональные возможности псевдостолбца Oracle rownum.

select row_number() over() as rownum,* from table1 inner join table2 on table1.id = table2.table1Id where table1.col1 = 'TEST' and rownum <=5 order by table2.col1;

Answer 2

Чтобы получить желаемое поведение, вы можете использовать такой подзапрос:

SELECT * FROM (
    SELECT table1.col1 as t1col1, table2.col1 as t2col1 
    FROM table1 INNER JOIN table2 ON table1.id = table2.table1Id
    WHERE table1.col1 = 'TEST'
    LIMIT 5
) AS sub
ORDER BY t2col1;

Я назвал столбцы там, потому что в вашем примере обе таблицы имели столбец col1.

Однако обратите внимание, что без какого-либо упорядочения во внутреннем запросе выбор из 5 строк, которые вы получите, будет чисто случайным и может измениться.