SQL - Is column exclusion possible from 'SELECT' clause?

Question

I face this question every time when I do a lot of complex processing and lot of columns SELECT ed in a sub-query but finally need to show only few. Is there anyway SQL (Oracle or Microsoft or others) is thinking of having an (extra) clause to just ignore the columns not required.

;with t as (
   select col1, col2, col3, col4, col5, col6, col7, col8, col9, col10
   from orders_tbl
   where order_date > getdate() -- ex. T-sql
)
, s as (
select t.*, row_number() over(partition by col1 order by col8 DESC, col9) rn
from t
)
--
-- The problem is here: if i don't explicitly select the individual columns of "t" ,then it'll display the column "rn" as well which is not required.
-- 
   select col1, col2, col3, col4, col5, col6, col7, col8, col9, col10
   from s where rn = 1
   order by col1, col2

Now, imagine something like this -

with t as (
   select col1, col2, col3, col4, col5, col6, col7, col8, col9, col10
   from orders_tbl
   where order_date > getdate() -- ex. T-sql
)
, s as (
   select t.*, row_number() over(partition by col1 order by col8 DESC, col9) rn
from t
)
--
-- Note: the imaginary clause "exclude"
-- 
select *
from s exclude (rn) where rn = 1
order by col1, col2

Your thoughts please?

Answer 1

It would be nice if MS Sql Server supported something like a SELECT * EXCEPT col FROM tbl like Google BigQuery.
But currently that functionality isn't (yet?) implemented in MS Sql Server.

However, one can simplify that SQL. And use only 1 CTE.

Since a TOP 1 WITH TIES can be combined with an ORDER BY ROW_NUMBER() OVER (...).

That way you don't have an RN column to exclude from the final result.

with T as (
   select TOP 1 WITH TIES
    col1, col2, col3, col4, col5, col6, col7, col8, col9, col10
   from orders_tbl
   where order_date > getdate()
   ORDER BY row_number() over(partition by col1 order by col8 DESC, col9)
)
select *
from T
order by col1, col2;

Note that the CTE is only needed here because the final result still has to be ordered by col1, col2.

Side-note One:

For simple queries selecting the required fields in the outer-query seems to be used more often.

with CTE as (
   select *
   , row_number() over(partition by col1 order by col8 DESC, col9) as rn
   from orders_tbl
   where order_date > getdate()
)
select col1, col2, col3, col4, col5, col6, col7, col8, col9, col10
from CTE
where rn = 1
order by col1, col2;

Side-note Two:

I would love to see something like TeraData's QUALIFY clause added someday to the SQL Standard. It's a nice thing to have when there's a need to filter based on a window function like ROW_NUMBER or DENSE_RANK.

In TeraData that SQL could be golf-coded like this:

select col1, col2, col3, col4, col5, col6, col7, col8, col9, col10
from orders_tbl
where order_date > current_timestamp
QUALIFY row_number() over(partition by col1 order by col8 DESC, col9) = 1
order by col1, col2

Answer 2

One way is to select into a new table and then drop the columns:

select col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, row_number() over(partition by col1 order by col8 DESC, col9) rn
into #a
from orders_tbl
where order_date > getdate()

alter table #a drop column col1

select * from #a

Note that this is not optimal in performance, as you've already read and then deleted some data. But it proves handy for few data and on-the-fly queries.