Leaving a column out of 'select *'

Pawn · August 2018

I've found plenty of articles and suggestions that someone should never want to do this. I do. Really, I do.

Here's the scenario. I've got a query will roll up attributes based on the Category with the most recent effective date. That looks something like this:

select i."Item Nbr",attr.*
from items i
inner join
(
select *, rank() over(partition by "Category Nbr" order by "Effective Date" desc, "Item Nbr" desc) as Rnk
from items
) attr
on i."Category Nbr" = attr."Category Nbr"
and attr.Rnk = 1

The problem is, the outer query lists Item Nbr twice, and I don't want the second one. Items will have a variable amount of columns that is subject to change, so I don't want to specify all of my columns because this is going to go in a view that gets automatically recreated every night.

So it would help if I could do something like:
select i."Item Nbr",attr.* [except "Item Nbr"]

Either that, or being able to rename one column in the view and dropping the other column from the view as part of my nightly script. I'd be happy with either solution or something else that lets me do what I need without specifying all of the columns. Whatever it is, I should only end up with i."Item Nbr" and NOT attr."Item Nbr".

Thanks in advance for your help!

Jim_Knicely · August 2018

Hi,

Say I have this data:

dbadmin=> SELECT * FROM items;
 Item Nbr | Category Nbr | Effective Date
----------+--------------+----------------
        1 |            1 | 2018-08-13
        2 |            2 | 2018-08-13
        2 |            2 | 2018-08-14
        3 |            4 | 2018-08-13
        3 |            4 | 2018-08-03
        3 |            4 | 2018-08-28
(6 rows)

Your SQL results:

dbadmin=> select i."Item Nbr",attr.*
dbadmin-> from items i
dbadmin-> inner join
dbadmin-> (
dbadmin(> select *, rank() over(partition by "Category Nbr" order by "Effective Date" desc, "Item Nbr" desc) as Rnk
dbadmin(> from items
dbadmin(> ) attr
dbadmin-> on i."Category Nbr" = attr."Category Nbr"
dbadmin-> and attr.Rnk = 1;
 Item Nbr | Item Nbr | Category Nbr | Effective Date | Rnk
----------+----------+--------------+----------------+-----
        1 |        1 |            1 | 2018-08-13     |   1
        2 |        2 |            2 | 2018-08-14     |   1
        2 |        2 |            2 | 2018-08-14     |   1
        3 |        3 |            4 | 2018-08-28     |   1
        3 |        3 |            4 | 2018-08-28     |   1
        3 |        3 |            4 | 2018-08-28     |   1
(6 rows)

Is that what you want? Repeated rows?

Why not do this?

dbadmin=> SELECT * FROM items LIMIT 1 OVER (partition by "Item Nbr" ORDER BY "Effective Date" DESC);
 Item Nbr | Category Nbr | Effective Date
----------+--------------+----------------
        1 |            1 | 2018-08-13
        2 |            2 | 2018-08-14
        3 |            4 | 2018-08-28
(3 rows)

Pawn · August 2018

Imagine that Items has a lot more columns.
Say it has something like:
Item Nbr, Item Description, Category Nbr, Category Description, Dept Nbr, Dept Description, Unit Cost, Manufacturer, Color, Effective Date

Also, Item Nbr is unique. You won't see two records in select * from items where the item nbr is the same. However, two or three categories can be in an Item Nbr. Sometimes we want to see things at the Item detail level, but other times we want to use a view of Items where the other fields are rolled up to the Category level.

This is mock-up data for example purposes. I know the first thing that comes to anyone's mind is that there's no reason anyone should want to do this, but there is.

The reason I don't want to specify my columns in the top select-statement is because this query will be used in a place where I don't want to have to touch it again after Items grows in the future to include more columns.

Jim_Knicely · August 2018

Hi,

One way would be to create a separate table without the "Item Nbr" column and limited rows partitioned by "Category Nbr" having the latest "Effective Date"... Then join this new table to the original.

Example:

dbadmin=> SELECT * FROM items;
 Item Nbr | Item Description | Category Nbr | Category Description | Dept Nbr | Dept Description | Unit Cost | Manufacturer | Color | Effective Date
----------+------------------+--------------+----------------------+----------+------------------+-----------+--------------+-------+----------------
        1 | Green Keyboard   |            1 | Keyboards            |       15 | Electronics      | $50       | Razer        | Green | 2015-03-05
        2 | Green Keyboard   |            1 | Keyboards            |       15 | Electronics      | $45       | Dell         | Green | 2015-02-19
        3 | Blue Keyboard    |            1 | Keyboards            |       16 | Keyboards        | $45       | Dell         | Blue  | 2016-01-19
        4 | Mechanical       |            1 | Keyboards            |       16 | Keyboards        | $100      | Razer        | Black | 2017-05-10
        5 | Black Mouse      |            2 | Mice                 |       15 | Electronics      | $15       | Logitech     | Black | 2017-05-17
        6 | White Mouse      |            2 | Mice                 |       15 | Electronics      | $14       | Dell         | White | 2016-04-19
        7 | Red Mouse        |            2 | Mice                 |       15 | Electronics      | $17       | Razer        | Red   | 2017-05-01
(7 rows)

dbadmin=> CREATE TABLE items_temp AS SELECT * FROM (SELECT * FROM items LIMIT 1 OVER (PARTITION BY "Category Nbr" ORDER BY "Effective Date" DESC, "Item Nbr" DESC)) foo ORDER BY 2 UNSEGMENTED ALL NODES;
CREATE TABLE

dbadmin=> ALTER TABLE items_temp DROP COLUMN "Item Nbr";
ALTER TABLE

dbadmin=> SELECT * FROM items_temp;
 Item Description | Category Nbr | Category Description | Dept Nbr | Dept Description | Unit Cost | Manufacturer | Color | Effective Date
------------------+--------------+----------------------+----------+------------------+-----------+--------------+-------+----------------
 Black Mouse      |            2 | Mice                 |       15 | Electronics      | $15       | Logitech     | Black | 2017-05-17
 Mechanical       |            1 | Keyboards            |       16 | Keyboards        | $100      | Razer        | Black | 2017-05-10
(2 rows)

dbadmin=> SELECT a."Item Nbr", b.* FROM items a JOIN items_temp b USING ("Category Nbr") ORDER BY 1;
 Item Nbr | Item Description | Category Nbr | Category Description | Dept Nbr | Dept Description | Unit Cost | Manufacturer | Color | Effective Date
----------+------------------+--------------+----------------------+----------+------------------+-----------+--------------+-------+----------------
        1 | Mechanical       |            1 | Keyboards            |       16 | Keyboards        | $100      | Razer        | Black | 2017-05-10
        2 | Mechanical       |            1 | Keyboards            |       16 | Keyboards        | $100      | Razer        | Black | 2017-05-10
        3 | Mechanical       |            1 | Keyboards            |       16 | Keyboards        | $100      | Razer        | Black | 2017-05-10
        4 | Mechanical       |            1 | Keyboards            |       16 | Keyboards        | $100      | Razer        | Black | 2017-05-10
        5 | Black Mouse      |            2 | Mice                 |       15 | Electronics      | $15       | Logitech     | Black | 2017-05-17
        6 | Black Mouse      |            2 | Mice                 |       15 | Electronics      | $15       | Logitech     | Black | 2017-05-17
        7 | Black Mouse      |            2 | Mice                 |       15 | Electronics      | $15       | Logitech     | Black | 2017-05-17
(7 rows)

Jim_Knicely · August 2018

Or... Since you said that you are re-creating the view via a nightly script, maybe the following suggestion can work for you?

Create the DDL for the view dynamically, then pipe the DDL into vsql to create the view:

[dbadmin@s18384357 ~]$ vsql -atc "SELECT 'CREATE OR REPLACE VIEW items_vw AS SELECT ' UNION ALL (SELECT 'a.\"Item Nbr\", b.* FROM (SELECT \"Item Nbr\", \"Category Nbr\" FROM items) a JOIN (SELECT') UNION ALL (SELECT '\"' || column_name || '\"' || NVL2(LEAD(column_name) OVER(ORDER BY ordinal_position), ',', '') FROM columns WHERE table_name = 'items' AND column_name <> 'Item Nbr' ORDER BY ordinal_position) UNION ALL (SELECT 'FROM items LIMIT 1 OVER (PARTITION BY \"Category Nbr\" ORDER BY \"Effective Date\" DESC, \"Item Nbr\" DESC)) b USING (\"Category Nbr\") ORDER BY 1;');" | vsql
                                                                  ?column?
---------------------------------------------------------------------------------------------------------------------------------------------
 CREATE OR REPLACE VIEW items_vw AS SELECT
 a."Item Nbr", b.* FROM (SELECT "Item Nbr", "Category Nbr" FROM items) a JOIN (SELECT
 "Item Description",
 "Category Nbr",
 "Category Description",
 "Dept Nbr",
 "Dept Description",
 "Unit Cost",
 "Manufacturer",
 "Color",
 "Effective Date"
 FROM items LIMIT 1 OVER (PARTITION BY "Category Nbr" ORDER BY "Effective Date" DESC, "Item Nbr" DESC)) b USING ("Category Nbr") ORDER BY 1;
(12 rows)

CREATE VIEW

Now I can select from the view and get the results you specified:

[dbadmin@s18384357 ~]$ vsql -c "SELECT * FROM items_vw;"
 Item Nbr | Item Description | Category Nbr | Category Description | Dept Nbr | Dept Description | Unit Cost | Manufacturer | Color | Effective Date
----------+------------------+--------------+----------------------+----------+------------------+-----------+--------------+-------+----------------
        1 | Mechanical       |            1 | Keyboards            |       16 | Keyboards        | $100      | Razer        | Black | 2017-05-10
        2 | Mechanical       |            1 | Keyboards            |       16 | Keyboards        | $100      | Razer        | Black | 2017-05-10
        3 | Mechanical       |            1 | Keyboards            |       16 | Keyboards        | $100      | Razer        | Black | 2017-05-10
        4 | Mechanical       |            1 | Keyboards            |       16 | Keyboards        | $100      | Razer        | Black | 2017-05-10
        5 | Black Mouse      |            2 | Mice                 |       15 | Electronics      | $15       | Logitech     | Black | 2017-05-17
        6 | Black Mouse      |            2 | Mice                 |       15 | Electronics      | $15       | Logitech     | Black | 2017-05-17
        7 | Black Mouse      |            2 | Mice                 |       15 | Electronics      | $15       | Logitech     | Black | 2017-05-17
(7 rows)

This is a better option as there is no need for a temporary table

Pieter_Sheth-Vo · November 2022

In 2022, is there a way to leave a column out of select?

The use case is defining tables and views that may have hundreds or thousands of columns, and the names of which are not known until query time, so its sooo much simpler to exclude the one column that we don't want rather than list the many unknown columns we do.

CREATE VIEW  combined AS 
SELECT * [excluding v.YEAR, t.YEAR]
FROM patients  p
LEFT JOIN visits USING (patient_id) v
LEFT JOIN treatments USING (visit_id) t

Or equivalently, is there a way to tell it to ignore duplicate column errors, and proceed using one of them? In pretty much every case, the duplicate columns are duplicate columns, so it doesn't matter which one we keep.

ERROR: View definition cannot contain duplicate column names "YEAR"

Leaving a column out of 'select *'

Comments

Leave a Comment