Leaving a column out of 'select *'

PawnPawn Registered User

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!

Comments

  • Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert

    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)
    
  • PawnPawn Registered User

    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.

    So if you had
    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|3/5/2015
    2|Green Keyboard|1|Keyboards|15|Electronics|$45 |Dell|Green|2/19/2015
    3|Blue Keyboard|1|Keyboards|16|Keyboards|$45 |Dell|Blue|1/19/2016
    4|Mechanical|1|Keyboards|16|Keyboards|$100 |Razer|Black|5/10/2017
    5|Black Mouse|2|Mice|15|Electronics|$15 |Logitech|Black|5/17/2017
    6|White Mouse|2|Mice|15|Electronics|$14 |Dell|White|4/19/2016
    7|Red Mouse|2|Mice|15|Electronics|$17 |Razer|Red|5/1/2017

    You'd want the view to end up looking like:
    Item Nbr|Item Description|Category Nbr|Category Description|Dept Nbr|Dept Description|Unit Cost|Manufacturer|Color|Effective Date
    1|Mechanical|1|Keyboards|16|Keyboards|$101 |Razer|Black|5/10/2017
    2|Mechanical|1|Keyboards|16|Keyboards|$101 |Razer|Black|5/10/2017
    3|Mechanical|1|Keyboards|16|Keyboards|$101 |Razer|Black|5/10/2017
    4|Mechanical|1|Keyboards|16|Keyboards|$101 |Razer|Black|5/10/2017
    5|Black Mouse|2|Mice|15|Electronics|$15 |Logitech|Black|5/17/2017
    6|Black Mouse|2|Mice|15|Electronics|$15 |Logitech|Black|5/17/2017
    7|Black Mouse|2|Mice|15|Electronics|$15 |Logitech|Black|5/17/2017

    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_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert
    edited 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_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert
    edited 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:

    [[email protected] ~]$ 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:

    [[email protected] ~]$ 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 :)

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file