Sometimes it would be easier to
SELECT saying what columns we don’t want, not those we do. Using my SQM_UTIL package, a very simple SQL macro will let us do that.
A common requirement
There are loads of “top-N” SQL queries that use analytic functions (also called window functions) to identify the rows we want. For example, suppose we want the highest paid employee in each department:
- Start by ranking the employees by salary (descending), commission (descending), then maybe hiredate (descending) – since a younger employee with the same salary is better paid, in my opinion;
- Then keep only the employees who have rank 1.
select * from ( select rank() over( partition by deptno order by sal desc, comm desc nulls last, hiredate desc ) rnk, emp.* from emp ) where rnk = 1
Generally, we don’t really want to see the
RNK column in the output, so we have to list all the other columns explicitly – or we can write a SQL macro to exclude just the column we want. Using the procedure
SQM_UTIL.COL_COLUMN_NAMES to do the heavy lifting, this macro is trivial:
create or replace function query_excluding( p_table in dbms_tf.table_t, p_exclude_cols in dbms_tf.columns_t ) return clob sql_macro is l_col_column_names varchar2(32767); begin sqm_util.col_column_names(p_table, l_col_column_names, p_exclude_cols); return 'select ' || l_col_column_names || ' from p_table'; end query_excluding; /
with data as ( select * from ( select rank() over( partition by deptno order by sal desc, comm desc nulls last, hiredate desc ) rnk, emp.* from emp ) where rnk = 1 ) select * from query_excluding( data, columns(rnk) );
Posts on SQL table macros
- SQL table macros 1: a moving target?
- SQL table macros 2: “polymorphic” is not “dynamic”
- SQL table macros 3: parameter strangeness
- SQL table macros 4: distribute into equal parts
- SQL table macros 5: dynamic but dangerous!
- SQL table macros 6: SQM_UTIL package
- SQL table macros 7: Select excluding
- SQL table macros 8: Print table
- SQL table macros 9: just pivot!
- SQL table macros 10: JUST_PIVOT examples
- SQL table macros 11: JUST_PIVOT for 21c (and 19c)
- SQL table macros 12: compare tables or queries
- SQL table macros 13: compare with primary key