KEEP в Oracle SQL. Введение
KEEP — часть синтаксиса агрегатных функций в Oracle SQL, позволяющая выбрать значение(я) из строк с минимальным или максимальным ранком по выражению сортировки.
Пишется как aggregate_function KEEP (DENSE_RANK {FIRST|LAST} ORDER BY expr) и часто используется для поиска «значения, связанного с минимальным/максимальным признаком» — например, имя сотрудника с максимальной зарплатой.
Где используют
- Arg‑min/arg‑max: вернуть атрибут (имя, id, дату) для строки с наибольшим/наименьшим показателем.
- Своды: «первая/последняя» покупка клиента, самый ранний/поздний статус заказа.
- Аналитика: в сочетании с
OVER (PARTITION BY …)вычислять «первое/последнее» по окну. - Разрешение тай‑брейков: при совпадениях используется плотный ранг — можно определить детерминизм через дополнительную сортировку.
Синтаксис
aggregate_function KEEP (DENSE_RANK {FIRST | LAST} ORDER BY sort_expr [ASC | DESC])
[ OVER (PARTITION BY expr1, expr2, ... [ ORDER BY ... ]) ]aggregate_function— обычноMIN,MAX, режеSUM,AVG,COUNT;FIRST/LAST— выбрать строки с минимальным/максимальным значениемsort_expr;DENSE_RANK— «плотный» ранг (без пропусков при тай‑брейках).
Пример шаблона: имя сотрудника с наибольшей зарплатой в департаменте —
MIN(emp_name) KEEP (DENSE_RANK LAST ORDER BY salary) OVER (PARTITION BY dept_id).
100 примеров
1. Имя сотрудника с максимальной зарплатой по отделам
SELECT
dept_id,
MIN(emp_name) KEEP (DENSE_RANK LAST ORDER BY salary) AS top_name
FROM employees
GROUP BY dept_id;2. Самый ранний найм: имя по отделам
SELECT
dept_id,
MIN(emp_name) KEEP (DENSE_RANK FIRST ORDER BY hire_date) AS first_hired_name
FROM employees
GROUP BY dept_id;3. Сумма продаж на последнюю дату по товару
SELECT
product_id,
SUM(amount) KEEP (DENSE_RANK LAST ORDER BY sales_date) AS amount_on_last_day
FROM sales
GROUP BY product_id;4. Выручка на первую дату по товару
SELECT
product_id,
SUM(amount) KEEP (DENSE_RANK FIRST ORDER BY sales_date) AS amount_on_first_day
FROM sales
GROUP BY product_id;5. Последний статус заказа (tie‑breaker по алфавиту)
SELECT
order_id,
MIN(status) KEEP (DENSE_RANK LAST ORDER BY status_ts) AS last_status
FROM order_status
GROUP BY order_id;6. Самый ранний статус заказа
SELECT
order_id,
MIN(status) KEEP (DENSE_RANK FIRST ORDER BY status_ts) AS first_status
FROM order_status
GROUP BY order_id;7. Актуальная цена по дате
SELECT
product_id,
MAX(price) KEEP (DENSE_RANK LAST ORDER BY price_date) AS latest_price
FROM price_history
GROUP BY product_id;8. Минимальная цена и дата (через два KEEP)
SELECT
product_id,
MIN(price) AS min_price,
MIN(price_date) KEEP (DENSE_RANK FIRST ORDER BY price) AS min_price_date
FROM price_history
GROUP BY product_id;9. Максимальная цена и её дата
SELECT
product_id,
MAX(price) AS max_price,
MIN(price_date) KEEP (DENSE_RANK LAST ORDER BY price) AS max_price_date
FROM price_history
GROUP BY product_id;10. Имя с максимальной зарплатой (окно по отделу)
SELECT
emp_id,
dept_id,
MIN(emp_name) KEEP (DENSE_RANK LAST ORDER BY salary)
OVER (PARTITION BY dept_id) AS top_name_in_dept
FROM employees;11. Первый заказ клиента (id)
SELECT
customer_id,
MIN(order_id) KEEP (DENSE_RANK FIRST ORDER BY order_date) AS first_order_id
FROM orders
GROUP BY customer_id;12. Сумма по последнему заказу клиента
SELECT
customer_id,
SUM(amount) KEEP (DENSE_RANK LAST ORDER BY order_date) AS last_order_amount
FROM orders
GROUP BY customer_id;13. Первый и последний логин (две колонки)
SELECT
user_id,
MIN(login_ts) KEEP (DENSE_RANK FIRST ORDER BY login_ts) AS first_login,
MIN(login_ts) KEEP (DENSE_RANK LAST ORDER BY login_ts) AS last_login
FROM logins
GROUP BY user_id;14. Самый ранний день рождения в команде (имя)
SELECT
team_id,
MIN(member_name) KEEP (DENSE_RANK FIRST ORDER BY birth_date) AS name_earliest_birth
FROM team_members
GROUP BY team_id;15. Значение метрики в последний момент часа
SELECT
device_id,
MIN(val) KEEP (DENSE_RANK LAST ORDER BY event_ts) AS last_val_in_hour
FROM events
GROUP BY device_id, TRUNC(event_ts, 'HH24');16. Значение метрики в начало часа
SELECT
device_id,
MIN(val) KEEP (DENSE_RANK FIRST ORDER BY event_ts) AS first_val_in_hour
FROM events
GROUP BY device_id, TRUNC(event_ts, 'HH24');17. Последний комментарий по тикету
SELECT
ticket_id,
MIN(comment_text) KEEP (DENSE_RANK LAST ORDER BY comment_ts) AS last_comment
FROM comments
GROUP BY ticket_id;18. Самый ранний комментарий по тикету
SELECT
ticket_id,
MIN(comment_text) KEEP (DENSE_RANK FIRST ORDER BY comment_ts) AS first_comment
FROM comments
GROUP BY ticket_id;19. Макс. температура и её время
SELECT
city_id,
MAX(temp_c) AS tmax,
MIN(measured_at) KEEP (DENSE_RANK LAST ORDER BY temp_c) AS tmax_time
FROM weather
GROUP BY city_id;20. Мин. температура и её время
SELECT
city_id,
MIN(temp_c) AS tmin,
MIN(measured_at) KEEP (DENSE_RANK FIRST ORDER BY temp_c) AS tmin_time
FROM weather
GROUP BY city_id;Еще 20 примеров.
21. Имя с минимальной зарплатой (DESC на сорте меняет смысл)
SELECT
dept_id,
MIN(emp_name) KEEP (DENSE_RANK LAST ORDER BY salary DESC) AS min_salary_name
FROM employees
GROUP BY dept_id;22. Имя с максимальной зарплатой (ASC в ORDER BY)
SELECT
dept_id,
MIN(emp_name) KEEP (DENSE_RANK LAST ORDER BY salary ASC) AS max_salary_name
FROM employees
GROUP BY dept_id;23. Последняя известная цена по партии
SELECT
batch_id,
MAX(price) KEEP (DENSE_RANK LAST ORDER BY price_ts) AS last_price
FROM batch_prices
GROUP BY batch_id;24. Дата первой транзакции по клиенту
SELECT
client_id,
MIN(tx_date) KEEP (DENSE_RANK FIRST ORDER BY tx_date) AS first_tx_date
FROM transactions
GROUP BY client_id;25. Последняя транзакция и сумма по клиенту
SELECT
client_id,
SUM(amount) KEEP (DENSE_RANK LAST ORDER BY tx_date) AS last_tx_amount
FROM transactions
GROUP BY client_id;26. Первое значение показателя в месяце
SELECT
account_id,
MIN(val) KEEP (DENSE_RANK FIRST ORDER BY ts) AS first_val_month
FROM metrics
GROUP BY account_id, TRUNC(ts, 'MM');27. Последнее значение показателя в месяце
SELECT
account_id,
MIN(val) KEEP (DENSE_RANK LAST ORDER BY ts) AS last_val_month
FROM metrics
GROUP BY account_id, TRUNC(ts, 'MM');28. Имя автора последней правки документа
SELECT
doc_id,
MIN(author) KEEP (DENSE_RANK LAST ORDER BY edited_at) AS last_editor
FROM revisions
GROUP BY doc_id;29. Имя автора первой версии документа
SELECT
doc_id,
MIN(author) KEEP (DENSE_RANK FIRST ORDER BY edited_at) AS first_author
FROM revisions
GROUP BY doc_id;30. Актуальный баланс по счёту (последняя дата)
SELECT
account_id,
SUM(balance) KEEP (DENSE_RANK LAST ORDER BY as_of_date) AS latest_balance
FROM balances
GROUP BY account_id;31. Начальный баланс по счёту (первая дата)
SELECT
account_id,
SUM(balance) KEEP (DENSE_RANK FIRST ORDER BY as_of_date) AS opening_balance
FROM balances
GROUP BY account_id;32. Первый визит пользователя (время)
SELECT
user_id,
MIN(visit_ts) KEEP (DENSE_RANK FIRST ORDER BY visit_ts) AS first_visit
FROM visits
GROUP BY user_id;33. Последний визит пользователя (время)
SELECT
user_id,
MIN(visit_ts) KEEP (DENSE_RANK LAST ORDER BY visit_ts) AS last_visit
FROM visits
GROUP BY user_id;34. Самая ранняя цена и сумма продаж в этот день
SELECT
product_id,
MIN(price) KEEP (DENSE_RANK FIRST ORDER BY price_date) AS earliest_price,
SUM(amount) KEEP (DENSE_RANK FIRST ORDER BY price_date) AS amount_that_day
FROM price_history
GROUP BY product_id;35. Самая поздняя цена и сумма продаж в этот день
SELECT
product_id,
MAX(price) KEEP (DENSE_RANK LAST ORDER BY price_date) AS latest_price,
SUM(amount) KEEP (DENSE_RANK LAST ORDER BY price_date) AS amount_that_day
FROM price_history
GROUP BY product_id;36. Оконный arg‑max по продукту (по датам)
SELECT
product_id, sales_date, amount,
MIN(amount) KEEP (DENSE_RANK LAST ORDER BY amount)
OVER (PARTITION BY product_id) AS top_amount_in_product
FROM sales;37. Оконный arg‑min по продукту (по датам)
SELECT
product_id, sales_date, amount,
MIN(amount) KEEP (DENSE_RANK FIRST ORDER BY amount)
OVER (PARTITION BY product_id) AS low_amount_in_product
FROM sales;38. Имя автора самой свежей версии по проекту
SELECT
project_id,
MIN(author) KEEP (DENSE_RANK LAST ORDER BY version_ts) AS latest_author
FROM versions
GROUP BY project_id;39. Имя автора самой ранней версии по проекту
SELECT
project_id,
MIN(author) KEEP (DENSE_RANK FIRST ORDER BY version_ts) AS earliest_author
FROM versions
GROUP BY project_id;40. Имя сотрудника с максимальной зарплатой по отделам
SELECT
dept_id,
MIN(emp_name) KEEP (DENSE_RANK LAST ORDER BY salary) AS top_name
FROM employees
GROUP BY dept_id;Еще 20 примеров.
41. Самый ранний найм: имя по отделам
SELECT
dept_id,
MIN(emp_name) KEEP (DENSE_RANK FIRST ORDER BY hire_date) AS first_hired_name
FROM employees
GROUP BY dept_id;42. Сумма продаж на последнюю дату по товару
SELECT
product_id,
SUM(amount) KEEP (DENSE_RANK LAST ORDER BY sales_date) AS amount_on_last_day
FROM sales
GROUP BY product_id;43. Выручка на первую дату по товару
SELECT
product_id,
SUM(amount) KEEP (DENSE_RANK FIRST ORDER BY sales_date) AS amount_on_first_day
FROM sales
GROUP BY product_id;44. Последний статус заказа (tie‑breaker по алфавиту)
SELECT
order_id,
MIN(status) KEEP (DENSE_RANK LAST ORDER BY status_ts) AS last_status
FROM order_status
GROUP BY order_id;45. Самый ранний статус заказа
SELECT
order_id,
MIN(status) KEEP (DENSE_RANK FIRST ORDER BY status_ts) AS first_status
FROM order_status
GROUP BY order_id;46. Актуальная цена по дате
SELECT
product_id,
MAX(price) KEEP (DENSE_RANK LAST ORDER BY price_date) AS latest_price
FROM price_history
GROUP BY product_id;47. Минимальная цена и дата (через два KEEP)
SELECT
product_id,
MIN(price) AS min_price,
MIN(price_date) KEEP (DENSE_RANK FIRST ORDER BY price) AS min_price_date
FROM price_history
GROUP BY product_id;48. Максимальная цена и её дата
SELECT
product_id,
MAX(price) AS max_price,
MIN(price_date) KEEP (DENSE_RANK LAST ORDER BY price) AS max_price_date
FROM price_history
GROUP BY product_id;49. Имя с максимальной зарплатой (окно по отделу)
SELECT
emp_id,
dept_id,
MIN(emp_name) KEEP (DENSE_RANK LAST ORDER BY salary)
OVER (PARTITION BY dept_id) AS top_name_in_dept
FROM employees;50. Первый заказ клиента (id)
SELECT
customer_id,
MIN(order_id) KEEP (DENSE_RANK FIRST ORDER BY order_date) AS first_order_id
FROM orders
GROUP BY customer_id;51. Сумма по последнему заказу клиента
SELECT
customer_id,
SUM(amount) KEEP (DENSE_RANK LAST ORDER BY order_date) AS last_order_amount
FROM orders
GROUP BY customer_id;52. Первый и последний логин (две колонки)
SELECT
user_id,
MIN(login_ts) KEEP (DENSE_RANK FIRST ORDER BY login_ts) AS first_login,
MIN(login_ts) KEEP (DENSE_RANK LAST ORDER BY login_ts) AS last_login
FROM logins
GROUP BY user_id;53. Самый ранний день рождения в команде (имя)
SELECT
team_id,
MIN(member_name) KEEP (DENSE_RANK FIRST ORDER BY birth_date) AS name_earliest_birth
FROM team_members
GROUP BY team_id;54. Значение метрики в последний момент часа
SELECT
device_id,
MIN(val) KEEP (DENSE_RANK LAST ORDER BY event_ts) AS last_val_in_hour
FROM events
GROUP BY device_id, TRUNC(event_ts, 'HH24');55. Значение метрики в начало часа
SELECT
device_id,
MIN(val) KEEP (DENSE_RANK FIRST ORDER BY event_ts) AS first_val_in_hour
FROM events
GROUP BY device_id, TRUNC(event_ts, 'HH24');56. Последний комментарий по тикету
SELECT
ticket_id,
MIN(comment_text) KEEP (DENSE_RANK LAST ORDER BY comment_ts) AS last_comment
FROM comments
GROUP BY ticket_id;57. Самый ранний комментарий по тикету
SELECT
ticket_id,
MIN(comment_text) KEEP (DENSE_RANK FIRST ORDER BY comment_ts) AS first_comment
FROM comments
GROUP BY ticket_id;58. Макс. температура и её время
SELECT
city_id,
MAX(temp_c) AS tmax,
MIN(measured_at) KEEP (DENSE_RANK LAST ORDER BY temp_c) AS tmax_time
FROM weather
GROUP BY city_id;59. Мин. температура и её время
SELECT
city_id,
MIN(temp_c) AS tmin,
MIN(measured_at) KEEP (DENSE_RANK FIRST ORDER BY temp_c) AS tmin_time
FROM weather
GROUP BY city_id;60. Имя с минимальной зарплатой (DESC на сорте меняет смысл)
SELECT
dept_id,
MIN(emp_name) KEEP (DENSE_RANK LAST ORDER BY salary DESC) AS min_salary_name
FROM employees
GROUP BY dept_id;Еще 20 примеров.
61. Имя с максимальной зарплатой (ASC в ORDER BY)
SELECT
dept_id,
MIN(emp_name) KEEP (DENSE_RANK LAST ORDER BY salary ASC) AS max_salary_name
FROM employees
GROUP BY dept_id;62. Последняя известная цена по партии
SELECT
batch_id,
MAX(price) KEEP (DENSE_RANK LAST ORDER BY price_ts) AS last_price
FROM batch_prices
GROUP BY batch_id;63. Дата первой транзакции по клиенту
SELECT
client_id,
MIN(tx_date) KEEP (DENSE_RANK FIRST ORDER BY tx_date) AS first_tx_date
FROM transactions
GROUP BY client_id;64. Последняя транзакция и сумма по клиенту
SELECT
client_id,
SUM(amount) KEEP (DENSE_RANK LAST ORDER BY tx_date) AS last_tx_amount
FROM transactions
GROUP BY client_id;65. Первое значение показателя в месяце
SELECT
account_id,
MIN(val) KEEP (DENSE_RANK FIRST ORDER BY ts) AS first_val_month
FROM metrics
GROUP BY account_id, TRUNC(ts, 'MM');66. Последнее значение показателя в месяце
SELECT
account_id,
MIN(val) KEEP (DENSE_RANK LAST ORDER BY ts) AS last_val_month
FROM metrics
GROUP BY account_id, TRUNC(ts, 'MM');67. Имя автора последней правки документа
SELECT
doc_id,
MIN(author) KEEP (DENSE_RANK LAST ORDER BY edited_at) AS last_editor
FROM revisions
GROUP BY doc_id;68. Имя автора первой версии документа
SELECT
doc_id,
MIN(author) KEEP (DENSE_RANK FIRST ORDER BY edited_at) AS first_author
FROM revisions
GROUP BY doc_id;69. Актуальный баланс по счёту (последняя дата)
SELECT
account_id,
SUM(balance) KEEP (DENSE_RANK LAST ORDER BY as_of_date) AS latest_balance
FROM balances
GROUP BY account_id;70. Начальный баланс по счёту (первая дата)
SELECT
account_id,
SUM(balance) KEEP (DENSE_RANK FIRST ORDER BY as_of_date) AS opening_balance
FROM balances
GROUP BY account_id;71. Первый визит пользователя (время)
SELECT
user_id,
MIN(visit_ts) KEEP (DENSE_RANK FIRST ORDER BY visit_ts) AS first_visit
FROM visits
GROUP BY user_id;72. Последний визит пользователя (время)
SELECT
user_id,
MIN(visit_ts) KEEP (DENSE_RANK LAST ORDER BY visit_ts) AS last_visit
FROM visits
GROUP BY user_id;73. Самая ранняя цена и сумма продаж в этот день
SELECT
product_id,
MIN(price) KEEP (DENSE_RANK FIRST ORDER BY price_date) AS earliest_price,
SUM(amount) KEEP (DENSE_RANK FIRST ORDER BY price_date) AS amount_that_day
FROM price_history
GROUP BY product_id;74. Самая поздняя цена и сумма продаж в этот день
SELECT
product_id,
MAX(price) KEEP (DENSE_RANK LAST ORDER BY price_date) AS latest_price,
SUM(amount) KEEP (DENSE_RANK LAST ORDER BY price_date) AS amount_that_day
FROM price_history
GROUP BY product_id;75. Оконный arg‑max по продукту (по датам)
SELECT
product_id, sales_date, amount,
MIN(amount) KEEP (DENSE_RANK LAST ORDER BY amount)
OVER (PARTITION BY product_id) AS top_amount_in_product
FROM sales;76. Оконный arg‑min по продукту (по датам)
SELECT
product_id, sales_date, amount,
MIN(amount) KEEP (DENSE_RANK FIRST ORDER BY amount)
OVER (PARTITION BY product_id) AS low_amount_in_product
FROM sales;77. Имя автора самой свежей версии по проекту
SELECT
project_id,
MIN(author) KEEP (DENSE_RANK LAST ORDER BY version_ts) AS latest_author
FROM versions
GROUP BY project_id;78. Имя автора самой ранней версии по проекту
SELECT
project_id,
MIN(author) KEEP (DENSE_RANK FIRST ORDER BY version_ts) AS earliest_author
FROM versions
GROUP BY project_id;79. Имя сотрудника с максимальной зарплатой по отделам
SELECT
dept_id,
MIN(emp_name) KEEP (DENSE_RANK LAST ORDER BY salary) AS top_name
FROM employees
GROUP BY dept_id;80. Самый ранний найм: имя по отделам
SELECT
dept_id,
MIN(emp_name) KEEP (DENSE_RANK FIRST ORDER BY hire_date) AS first_hired_name
FROM employees
GROUP BY dept_id;Еще 20 примеров.
81. Сумма продаж на последнюю дату по товару
SELECT
product_id,
SUM(amount) KEEP (DENSE_RANK LAST ORDER BY sales_date) AS amount_on_last_day
FROM sales
GROUP BY product_id;82. Выручка на первую дату по товару
SELECT
product_id,
SUM(amount) KEEP (DENSE_RANK FIRST ORDER BY sales_date) AS amount_on_first_day
FROM sales
GROUP BY product_id;83. Последний статус заказа (tie‑breaker по алфавиту)
SELECT
order_id,
MIN(status) KEEP (DENSE_RANK LAST ORDER BY status_ts) AS last_status
FROM order_status
GROUP BY order_id;84. Самый ранний статус заказа
SELECT
order_id,
MIN(status) KEEP (DENSE_RANK FIRST ORDER BY status_ts) AS first_status
FROM order_status
GROUP BY order_id;85. Актуальная цена по дате
SELECT
product_id,
MAX(price) KEEP (DENSE_RANK LAST ORDER BY price_date) AS latest_price
FROM price_history
GROUP BY product_id;86. Минимальная цена и дата (через два KEEP)
SELECT
product_id,
MIN(price) AS min_price,
MIN(price_date) KEEP (DENSE_RANK FIRST ORDER BY price) AS min_price_date
FROM price_history
GROUP BY product_id;87. Максимальная цена и её дата
SELECT
product_id,
MAX(price) AS max_price,
MIN(price_date) KEEP (DENSE_RANK LAST ORDER BY price) AS max_price_date
FROM price_history
GROUP BY product_id;88. Имя с максимальной зарплатой (окно по отделу)
SELECT
emp_id,
dept_id,
MIN(emp_name) KEEP (DENSE_RANK LAST ORDER BY salary)
OVER (PARTITION BY dept_id) AS top_name_in_dept
FROM employees;89. Первый заказ клиента (id)
SELECT
customer_id,
MIN(order_id) KEEP (DENSE_RANK FIRST ORDER BY order_date) AS first_order_id
FROM orders
GROUP BY customer_id;90. Сумма по последнему заказу клиента
SELECT
customer_id,
SUM(amount) KEEP (DENSE_RANK LAST ORDER BY order_date) AS last_order_amount
FROM orders
GROUP BY customer_id;91. Первый и последний логин (две колонки)
SELECT
user_id,
MIN(login_ts) KEEP (DENSE_RANK FIRST ORDER BY login_ts) AS first_login,
MIN(login_ts) KEEP (DENSE_RANK LAST ORDER BY login_ts) AS last_login
FROM logins
GROUP BY user_id;92. Самый ранний день рождения в команде (имя)
SELECT
team_id,
MIN(member_name) KEEP (DENSE_RANK FIRST ORDER BY birth_date) AS name_earliest_birth
FROM team_members
GROUP BY team_id;93. Значение метрики в последний момент часа
SELECT
device_id,
MIN(val) KEEP (DENSE_RANK LAST ORDER BY event_ts) AS last_val_in_hour
FROM events
GROUP BY device_id, TRUNC(event_ts, 'HH24');94. Значение метрики в начало часа
SELECT
device_id,
MIN(val) KEEP (DENSE_RANK FIRST ORDER BY event_ts) AS first_val_in_hour
FROM events
GROUP BY device_id, TRUNC(event_ts, 'HH24');95. Последний комментарий по тикету
SELECT
ticket_id,
MIN(comment_text) KEEP (DENSE_RANK LAST ORDER BY comment_ts) AS last_comment
FROM comments
GROUP BY ticket_id;96. Самый ранний комментарий по тикету
SELECT
ticket_id,
MIN(comment_text) KEEP (DENSE_RANK FIRST ORDER BY comment_ts) AS first_comment
FROM comments
GROUP BY ticket_id;97. Макс. температура и её время
SELECT
city_id,
MAX(temp_c) AS tmax,
MIN(measured_at) KEEP (DENSE_RANK LAST ORDER BY temp_c) AS tmax_time
FROM weather
GROUP BY city_id;98. Мин. температура и её время
SELECT
city_id,
MIN(temp_c) AS tmin,
MIN(measured_at) KEEP (DENSE_RANK FIRST ORDER BY temp_c) AS tmin_time
FROM weather
GROUP BY city_id;99. Имя с минимальной зарплатой (DESC на сорте меняет смысл)
SELECT
dept_id,
MIN(emp_name) KEEP (DENSE_RANK LAST ORDER BY salary DESC) AS min_salary_name
FROM employees
GROUP BY dept_id;100. Имя с максимальной зарплатой (ASC в ORDER BY)
SELECT
dept_id,
MIN(emp_name) KEEP (DENSE_RANK LAST ORDER BY salary ASC) AS max_salary_name
FROM employees
GROUP BY dept_id;Частые ошибки и подводные камни
- Путаница с FIRST/LAST.
FIRST— минимальное поORDER BY,LAST— максимальное; направление меняется опциейASC/DESC. - Детерминизм при тай‑брейках. Добавляйте второй ключ сортировки (например,
ORDER BY salary DESC, emp_id), чтобы результат не зависел от коллатора имени. - Производительность. В групповых сводах
KEEPможет быть дороже простой оконной функции +FETCH FIRST 1 ROW WITH TIES. Сравнивайте планы. - Семантика агрегации.
SUM ... KEEPсуммирует по строкам «первого/последнего» ранга, а не весь набор. - Смешение типов. Следите за типами в
ORDER BYи агрегате (строки vs числа) и приводите явно при необходимости.
Альтернативы
FIRST_VALUE/LAST_VALUEкак оконные функции (нужны рамки окна).ROW_NUMBERв подзапросе + фильтрWHERE rn = 1.FETCH FIRST 1 ROW WITH TIESповерхORDER BY(для выборки строк).
Заключение
KEEP даёт краткую запись для «первого/последнего» значения в агрегатах и окнах. Оно удобно для arg‑min/arg‑max сценариев, а при сложных правилах сортировки сочетайте с дополнительными ключами и оконными функциями.
Документация
Oracle SQL Language Reference — Aggregate Functions (KEEP)
Oracle SQL — First/Last Aggregate Functions
Следующая статья:
PRIOR в Oracle SQL — как строить иерархические связи между строками