How do we remove the column that is created by LEAD function

Question

When I run run below query

SELECT
    *
FROM
    (
        SELECT
            LEAD(hire_date)
            OVER(PARTITION BY department_id
                 ORDER BY
                     hire_date
            ) AS recent_joinee,
            a.*
        FROM
            employees a
    )
WHERE
    recent_joinee IS NULL;

I am getting below results

But i don't need the first column "Recent_joinee" as this is created by the lead function , Rest of "n" number of columns i need.

For this scenario what i need to do ?

Remove it form the query!? Or dont use `*` and specify the columns you want. — Reinis Verbelis, Aug 23 '21 at 06:38
Sadly, you are probably going to have to just list out the columns you want to select. There is no syntax in Oracle for selecting all columns minus some blacklist. — Tim Biegeleisen, Aug 23 '21 at 06:39
@Tim can i use any sub query or anything to achieve the result Like " sub string " or something like that ? — Erik, Aug 23 '21 at 07:07
Not just Oracle - the SQL language supports only explicit column lists or `*`, and as far as I know, there is no mainstream implementation that has an *"every column except..."* syntax. I agree that would be useful. (Edit: turns out Google Big Query implements `SELECT * EXCEPT`.) See also https://stackoverflow.com/q/413819/230471, https://stackoverflow.com/questions/29095281/how-to-select-all-the-columns-of-a-table-except-one-column — William Robertson, Aug 23 '21 at 10:26
@leaner There are some (inconvenient) solutions in [this question](https://stackoverflow.com/questions/9133120/can-you-select-everything-but-1-or-2-fields-without-writers-cramp). — Jon Heller, Aug 24 '21 at 00:46

score 2 · Accepted Answer · answered Aug 23 '21 at 06:45

2

You should specify the columns you need.

You can either get all fields (*) or specify the fields you want.

More info

answered Aug 23 '21 at 06:45

TimLer

1,320
2
16

score 1 · Answer 2 · answered Aug 23 '21 at 12:17

It can be cumbersome to list all the columns. Oracle doesn't have a simple way to remove columns. For your example, you can incur the overhead of an extra join (which isn't too much if the key is the primary key):

SELECT e.*
FROM employees e JOIN
     (SELECT LEAD(hire_date) OVER (PARTITION BY department_id
                                   ORDER BY hire_date
                                  ) AS recent_joinee,
             e2.*
      FROM employees e2
     ) e2
     USING (employee_id)
WHERE e2.recent_joinee IS NULL;

Alternatively, you could use a correlated subquery:

select e.*
from employee e
where not exists (select 1
                  from employee e2
                  where e2.department_id = e.department_id and
                        e2.hire_date > e.hire_date
                 );

if i use your correlated sub query i am getting duplicates, which is the persons who joined at same date in each department , why i am not getting that when i use LEAD function , is there a work around ? — Erik, Aug 23 '21 at 16:08
@leaner . . . It depends what you want. With `lead()` you have ties in the `order by` so the results are unstable (you can get any employee from the last day). The `not exists` version returns all such employees. — Gordon Linoff, Aug 23 '21 at 22:52

How do we remove the column that is created by LEAD function

2 Answers2