sql – Grouping with dependent maximization of two columns (no subqueries)

Question:

There is a task to display all rows, grouping the table by the t_name column, while maximizing by two columns t_date and t_num , so that

  1. first by maximizing on the t_num column
  2. then the remaining sample is maximized by t_date

The question is: can this be organized without subqueries (like window functions or other ways), and would it be faster?

For example

   t_name   t_date      t_num
1  aaa      20.01.2018  3
2  aaa      03.01.2018  10
3  aaa      01.01.2018  10
4  aaa      (null)      10
5  bbb      19.01.2018  1
6  bbb      (null)      10
  1. as a result of maximization over the t_num column, the values
   t_name   t_date      t_num
2  aaa      03.01.2018  10
3  aaa      01.01.2018  10
4  aaa      (null)      10
6  bbb      (null)      10
  1. then the selection is maximized on the t_date column and the values ​​remain
   t_name   t_date      t_num
2  aaa      03.01.2018  10
6  bbb      (null)      10

This can be easily done through subqueries

SELECT
  T.T_NAME,
  MAX(T.T_DATE) AS T_DATE,
  G.T_NUM 
FROM 
  TEST_GROUPING T
  INNER JOIN (
               SELECT T_NAME, MAX(T_NUM) AS T_NUM 
               FROM TEST_GROUPING 
               GROUP BY T_NAME
             ) G ON G.T_NAME = T.T_NAME AND G.T_NUM = T.T_NUM
GROUP BY
  T.T_NAME,
  G.T_NUM

Answer:

Yes, window functions are easy to do:

with t (t_name, t_date, t_num) as (
      select 'aaa', to_date('20.01.2018', 'dd.mm.yyyy'), 3 from dual union all
      select 'aaa', to_date('03.01.2018', 'dd.mm.yyyy'), 10 from dual union all
      select 'aaa', to_date('01.01.2018', 'dd.mm.yyyy'), 10 from dual union all
      select 'aaa', null, 10 from dual union all
      select 'bbb', to_date('20.01.2018', 'dd.mm.yyyy'), 1 from dual union all
      select 'bbb', null, 10 from dual)
select t_name, t_date, t_num
  from (select t_name, t_date, t_num,
               row_number() over (partition by t_name order by t_num desc, t_date desc nulls last) rn
          from t)
 where rn = 1

It should work quickly too, especially if you have an index by t_num , or better even by (t_num, t_date) .

Post Views: 2
Scroll to Top